OCP-047-152

SQL> SELECT product_name || q'(   it's not available for order')'  as product_name
  2  FROM product_information WHERE product_status = 'obsolete';

PRODUCT_NAME
----------------------------------------------------------------------------------
HD 10GB /I   it's not available for order'
HD 10GB /R   it's not available for order'
HD 10GB /S   it's not available for order'
HD 10GB @5400 /SE   it's not available for order'
HD 6GB /I   it's not available for order'
8MB EDO Memory   it's not available for order'
DIMM - 16 MB   it's not available for order'
RAM - 16 MB   it's not available for order'
Modem - 56/H/E   it's not available for order'
Laptop 16/8/110   it's not available for order'
Laptop 48/10/56/110   it's not available for order'

PRODUCT_NAME
----------------------------------------------------------------------------------
MB - S300   it's not available for order'
CD-ROM 8x   it's not available for order'
Monitor 21/HR/M   it's not available for order'
Compact 400/DQ   it's not available for order'
Router - DTMF4   it's not available for order'

16 rows selected.

可以看到放在括号里面的空格也都按照空格显示出来了。

152. Evaluate the following SQL statement: 
SELECT product_name || 'it's not available for order' 
FROM product_information WHERE product_status = 'obsolete';
You received the following error while executing the above query: 
ERROR:ORA-01756:quoted string not properly terminated 
What would you do to execute the query successfully? 
A. Enclose the character literal string in the SELECT clause within the double 
quotation marks. 
B. Do not enclose the character literal string in the SELECT clause within 
the single quotation marks. 
C. Use Quote (q) operator and delimiter to allow the use of single quotation 
mark in the literal character string. 
D. Use escape character to negate the single quotation mark inside the literal 
character string in the SELECT clause.

参考文章：Q - quoting mechanism for string literals

We make use of single quotation mark in SQL and PL/SQL to identify sting literals. If the literal itself
contains a single quote, we need to add one more quote next to it. This additional quote acts as
an escape character and removes conflict with the outside quotes that are enclosing the string.
Oracle realises that long complex strings having lot of single quotes can turn out to become
cumbersome and prone to errors that may not be caught during testing.
Release 10g onwards, a new quoting mechanism is provided in the form of  "q". This new quote
operator allows us to choose our own quotation mark delimiter. Here are some examples -SQL> select 'amar's web blog. It's personal..' str from dual;
select 'amar's web blog. It's personal..' str from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
What we normally do:-SQL> select 'amar''s web blog. It''s personal..' str from dual;
STR
--------------------------------amar's web blog. It's personal..
1 row selected.
Here is use of Q - quote operator. The above statement can also be represented as any one of the
below.
select q'(amar's web blog. It's personal.)'str from dual;
select q'[amar's web blog. It's personal.]'str from dual;
select q'Aamar's web blog. It's personal.A'str from dual;
select q'/amar's web blog. It's personal./'str from dual;
select q'Zamar's web blog. It's personal.Z'str from dual;
select q'|amar's web blog. It's personal.|'str from dual;
select q'+amar's web blog. It's personal.+'str from dual;
And so on.. After the Q-quote is specified, provide the single quote along with a unique character
at the beginning of the string. To close the string, provide the same character followed by the single
quote.  The single quote along with the provided character form the two character string
enclosure.
If you decide to use braces, Oracle expects matching enclosures, i.e., Open brackets should be
represented by closed brackets at the end of the string. Other characters can be represented as

they are at both ends.

All we need to take care of is that the last two character delimiter do not show up in the string itself.
For instance the below will throw error as the closing characters get repeated within the string.
SQL> select q'aamar's web blog. Ita's personal.a'str from dual;
ERROR:
ORA-01756: quoted string not properly terminated
The same can be used in PL/SQL also.
SQL> declare
2 l_str varchar2(100) := q'[amar's web blog. Ita's personal.]';
3 begin
4 dbms_output.put_line(l_str);
5 end;
6 /
amar's web blog. Ita's personal.
PL/SQL procedure successfully completed.
I recently came across a program that framed dynamic INSERT statement to copy data from one
database to another. Unfortunately, one of the record column had a single quote embedded in
the string and this resulted in the program unit to fail in production. This happened in release 9i and
the only alternative available was to remove/double the single quotes from the string literal. From
release 10g, Q-quote could also be used to prevent this problem. I will simulate the same scenario
and try this out.
SQL> create table am100(col1 number, col2 varchar2(100));
Table created.
SQL> insert into am100 values(1, q'[amar's web blog. It's personal]');
1 row created.
SQL> insert into am100 values(2, q'[this is a simple string]');
1 row created.
SQL> insert into am100 values(3, q'[this is just another string]');
1 row created.
SQL> select * from am100;
COL1 COL2
---------- ----------------------------------1 amar's web blog. It's personal
2 this is a simple string
3 this is just another string
3 rows selected.
SQL> commit;
Commit complete.

Now I will call a simple routine that will read the data and generate dynamic SQL for inserting into
another table.
declare
l_str varchar2(4000);
begin
for rec in (select col1, col2 from am100) loop
l_str := 'insert into am102(col1, col2) values (' || rec.col1 ||',''' ||
rec.col2 || ''')';
dbms_output.put_line(l_str);
execute immediate l_str;
end loop;
end;
/
The unit errors out as below, because of the single quote mismatch.
insert into am102(col1, col2) values (1,'amar's web blog. It's personal');
begin
*
ERROR at line 1:
ORA-00917: missing comma
ORA-06512: at line 4
I will now modify the program unit to add Q-quote. We can also use the REPLACE function to
remove or double-up single quote in strings, both options are given below.
[] With REPLACE function (doubling the single quote);
declare
l_str varchar2(4000);
begin
for rec in (select col1, replace(col2, '''', '''''') col2 from am100) loop
l_str := 'insert into am102(col1, col2) values (' 
|| rec.col1 ||',''' || rec.col2 || ''')';
dbms_output.put_line(l_str);
execute immediate l_str;
end loop;
end;
/
Output generated:-insert into am102(col1, col2) values (1,'amar''s web blog. It''s personal')
insert into am102(col1, col2) values (2,'this is a simple string')
insert into am102(col1, col2) values (3,'this is just another string')
[] With Q-quote
declare
l_str varchar2(4000);
Begin
for rec in (select col1, 'q''[' || col2 || ']''' col2 from am100) loop
l_str := 'insert into am102(col1, col2) values (' 
|| rec.col1 ||',' || rec.col2 || ')';
dbms_output.put_line( l_str );
execute immediate l_str;
end loop;
end;
/

Output generated:-insert into am102(col1, col2) values (1,q'[amar's web blog. It's personal]')
insert into am102(col1, col2) values (2,q'[this is a simple string]')
insert into am102(col1, col2) values (3,q'[this is just another string]')
Q-quote will be a handy option when we deal with huge text literals.