It’s the little things in life …

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

syntax


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;

class=”brush: sql”>


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):

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

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

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;

Source Article from http://rokitta.blogspot.com/2010/11/its-little-things-in-life.html
It’s the little things in life …
http://rokitta.blogspot.com/2010/11/its-little-things-in-life.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

Leave a Reply