Sometimes
its problematic to deal with single quotes in Oracle SQL which comes in
while writing some SQL codes . Below is the new Quotes Operator
introduced by oracle to overcome this :
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;
(New added)
select q'zamar's web blog. It's personal.z' 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 and 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 does
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
columns 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> create table am102(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]')
No comments:
Post a Comment