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.