Text Literal Structure

In a previous post on XMLType/XMLTransform and parameters, I'd discovered the q'{}' structure from the OTN forum post, but I didn't know much more about it. I wasn't sure what to go searching for so I left it as an interesting tidbit to figure out later.

Just about two months after that post (on Nov 4, 2010 to be exact), I encountered this syntax structure in a PL/SQL Challenge quiz. To see the actual quiz, you will need to register (free) on the site. If you are into learning more about the database from the SQL and PL/SQL side of things in a simple quiz that takes a few minutes of time each day, I strongly suggest signing up and participating. You can find his blog discussion of this particular quiz at The quote character (q) and the 4 November quiz.

Using his wording as a starting point to search the Oracle documentation, I soon came across Text Literals. Somewhere along the way I saw that this was a 10g (10.1 I believe) addition. I went from 8.1 to 10.2, so it explains why I missed it.

It's pretty simple to use. Ignoring the national character version, you start with
q''
Within that, you put your delimiters. As the documentation says,
If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.
so we can setup structures such as

• q'[]'
• q'<>'
• q'##'

and within our quote delimiter, we simply put the desired text. Some examples:

BEGIN
  dbms_output.put_line(q'{Here be the text with a ' in it}');
  dbms_output.put_line(q'*Here be the text with a ' in it*');
  dbms_output.put_line(q'#Random words put here#');
  dbms_output.put_line(q'^Here be the text with a ' in it^');
END;

which produces

Here be the text with a ' in it
Here be the text with a ' in it
Random words put here
Here be the text with a ' in it

Makes it a lot easier to put a single quote into a string than using double single quotes.

* Caveat *
Apparently the Text Literal process has issues with strings that contain 'n', where n' is at the end of the string.  By that I mean the following will all run successfully

select q'[help'n ']'
from dual;
select q'['na']'
from dual;
select q'['a']'
from dual;

but the following all return an ORA-01756: quoted string not properly terminated

select q'[help'n']'
from dual;
select q'['an']'
from dual;
select q'['n']'
from dual;

I've seen this error on both 10.2.0.4 and 11.1.0.6. I can't find anything in MOS yet on it.