It’s the little things in life …

Sometimes you get information and you ask yourself: "Why didn't I know this? This would have made (certain) things so much easier!" or "I didn't know I could do this". Well, this just happened to me and after asking a few colleges, I found out: I’m not the only Oracle developer who didn't know this (without being too concerned about the statistical relevance of the sample size, I would estimate that about 80% of PL/SQL developers might not know this). So I decided to write this little post.



Consider this piece of code:

CREATE TABLE examples (sample_str VARCHAR2 (100));

INSERT INTO examples (sample_str)
VALUES ('It''s the little things in life ...');

DECLARE
   v_stm VARCHAR2 (1000);
BEGIN
   v_stm := 'insert into examples (sample_str) values (''It''''s the little things in life ...'')';
   EXECUTE IMMEDIATE v_stm;
END;

Take a look at the quotation syntax. Most developers will recognize the double-quote as “escape” for the single quote to be able to insert/assign the quote as part of the string value. This syntax can get really messy, especially in combination with concatenations or nested quotes in dynamic SQL. Such code can be a real pain to debug and maintain. The wrong number of quote’s will result in a “ORA-01756: quoted string not properly terminated” error..

In 10g Oracle introduced a new quoting syntax. This new feature enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. To use this mechanism, simply prefix the string with a “q”. The syntax is q'[...]', where the "[" and "]" characters can be any of the following (as long as they do not already appear in the string):

• !
• {}
• []
• ()
• <>

Using this quoting syntax, the above example would look like this:

INSERT INTO examples (sample_str)
VALUES (q'[It's the little things in life ...]');

DECLARE
v_stm VARCHAR2 (1000);
BEGIN

v_stm := q'[insert into examples (sample_str) values ('It''s the little things in life ...')]';

EXECUTE IMMEDIATE v_stm;

END;

Looks a lot better, doesn’t it?! Well, there is still one of these double quoted syntax things in this example. Guess what: The mechanism even allows you to code nested single-quoting syntax:

DECLARE
v_stm VARCHAR2 (1000);
BEGIN

v_stm := q'[insert into examples (sample_str) values (q'<It's the little things in life ...>')]';

EXECUTE IMMEDIATE v_stm;

END;

Siquis in hoc artem populo non novit amandi, Hoc legat et lecto carmine doctus amet.