Alternative Quoting Mechanism (''Q'') for String Literals

View All Scripts Login and Run Script

Script Name Alternative Quoting Mechanism (''Q'') for String Literals
Description Oracle Database offers the ability, in both SQL and PL/SQL, to specify our own user-defined delimiters for string literals. Here's how it works: you prefix your literal with the letter "q". Then you type a single quote, followeed by your starting delimiter for the literal. Then you type your literal string without having to double up on your single quote characters. When you have typed in your full literal, terminate it with your ending delimiter, followed by a single quote. In other words, you will follow this general format: q'[your string here]' where "[" represents (and certainly could be used for) the starting delimiter, and "]" represents the ending delimiter. Oracle automatically recognizes "paired" delimiters, such as [], {}, (), and <>. If you want to use some other character as your start delimiter and it doesn't have a "natural" partner for termination, you must use the same character for start and end delimiters. Finally, if you choose a character for a delimiter and it appears in your string immediately before a single quotation mark, Oracle will be unhappy and raise an error.
Category PL/SQL General
Contributor Steven Feuerstein (Oracle)
Created Monday November 30, 2015

Statement 1
Sure you could do things the way you've always done them.
Traditional "Doubling-Up" of Single Quotes
```
BEGIN  
   DBMS_OUTPUT.put_line ('That''s a really funny ''joke''.');  
END; 
```
That's a really funny 'joke'.
Statement 2
Or you could use paired symbols, like [] () <>, so that any single quotes inside the literal do not need to be doubled.
Using Paired Symbols
```
BEGIN  
    DBMS_OUTPUT.PUT_LINE (  
       q'[What's a quote among friends?]');  
END; 
```
What's a quote among friends?
Statement 3
Or use a single character, in this case "!", to indicate the start and end of the literal.
Replacement for Single Quote
```
BEGIN  
    DBMS_OUTPUT.PUT_LINE (  
       q'!What's a quote among friends?!');  
END; 
```
What's a quote among friends?

Statement 4

BEGIN 
   DBMS_OUTPUT.put_line (q'(That's a really funny 'joke'.)'); 
END;

That's a really funny 'joke'.

Statement 5
I can't use the open paren symbol to both start and end the literal. If I start with "(", I need to end with ")".
Can't Use One Symbol from Pair
```
BEGIN  
   DBMS_OUTPUT.put_line (q'(That''s a really funny ''joke''.(');  
END; 
```
ORA-06550: line 2, column 26: PLS-00103: Encountered the symbol "That''s a really funny ''joke''.('); END;" when expecting one of the following: ( ) - + case mod new not null table continue avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timesta

Statement 6

BEGIN 
   DBMS_OUTPUT.put_line (q'#That's a really funny 'joke'.#'); 
END;

That's a really funny 'joke'.

Statement 7
The open and close characters must either be the same or they must be paired, such as ( and ). You cannot use two different characters, like # and $.
Mismatch!
BEGIN DBMS_OUTPUT.put_line (q'#That's a really funny 'joke'.$'); END;
ORA-06550: line 2, column 26: PLS-00103: Encountered the symbol "That's a really funny 'joke'.$'); END;" when expecting one of the following: ( ) - + case mod new not null table continue avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp
Statement 8
Works in SQL, Too
SELECT q''All the President's men'' FROM DUAL
Q''ALLTHEPRESIDENT'SMEN''
All the President's men
Statement 9
SELECT q'#All the President's men#' FROM DUAL
Q'#ALLTHEPRESIDENT'SMEN#'
All the President's men
Statement 10
A space cannot be used to start and end a literal.
Can't Use a Space
SELECT q' All the President's men ' FROM DUAL
ORA-00911: invalid character
Statement 11
The Q mechanism recognizes <> as a pair. This results in the situation that you cannot "lead" and then close with ">".
Can't Use Closing Symbol for Opening
SELECT q'>All the President's men>' FROM DUAL
Q'>ALLTHEPRESIDENT'SMEN>'
All the President's men
Statement 12
SELECT q'>All the President's men<' FROM DUAL
ORA-01756: quoted string not properly terminated
Statement 13
SELECT q'<All the President's men<' FROM DUAL
ORA-01756: quoted string not properly terminated
Statement 14
Interestingly, I can even use my alternative quoting character within the string - as long as it is not immediately followed by a single quote!
Use Alternative Character Inside String!
BEGIN dbms_output.put_line (q'PAll the President's menP'); END;
All the President's men

Q''ALLTHEPRESIDENT'SMEN''
All the President's men

Q'#ALLTHEPRESIDENT'SMEN#'
All the President's men

Q'>ALLTHEPRESIDENT'SMEN>'
All the President's men

Statement 15

Notice that | can be used as the quote delimiter as long as it's not followed by a single quote.

Works with Dynamic SQL

DECLARE  
   l_men_or_women VARCHAR2(5) := 'men';  
BEGIN  
   EXECUTE IMMEDIATE  
     q'|BEGIN  
           dbms_output.put_line('All the President''s |'||l_men_or_women||q'|');  
        END;  
      |';  
END;

All the President's men

Statement 16

BEGIN 
   DBMS_OUTPUT.put_line (q'['Hello,' said the man, who didn't like goodbyes.]'); 
END;

'Hello,' said the man, who didn't like goodbyes.

Statement 17

BEGIN 
   DBMS_OUTPUT.put_line (q'{'Hello,' said the man, who didn't like goodbyes.}'); 
END;

'Hello,' said the man, who didn't like goodbyes.

Statement 18

BEGIN 
   DBMS_OUTPUT.put_line (Q'('Hello,' said the man, who didn't like goodbyes.)'); 
END;

'Hello,' said the man, who didn't like goodbyes.

Statement 19

BEGIN 
   DBMS_OUTPUT.put_line (q'<'Hello,' said the man, who didn't like goodbyes.>'); 
END;

'Hello,' said the man, who didn't like goodbyes.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials