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

View All Scripts Login to 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.
Area PL/SQL General
Contributor Sarah Hirschfeld (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;

Statement processed.
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; 
```
```
Statement processed.
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;

Statement processed.
What's a quote among friends?

Statement 4

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

Statement processed.
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 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   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

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 6

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

Statement processed.
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 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   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

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 8

Works in SQL, Too

SELECT q''All the king's horses''   
 FROM  DUAL


Q''ALLTHEKING'SHORSES''
All the king's horses

Q''ALLTHEKING'SHORSES''
All the king's horses

Statement 9

SELECT q'#All the king's horses#'  
 FROM  DUAL


Q'#ALLTHEKING'SHORSES#'
All the king's horses

Statement 10
A space cannot be used to start and end a literal.
Can't Use a Space
```
SELECT q' All the king's horses '   
 FROM  DUAL
```
```
ORA-00911: invalid character
```
More Details: https://docs.oracle.com/error-help/db/ora-00911
Statement 11
The Q mechanism recognizes <> as a pair. However, you do have the option to "lead" and then close with the closing symbol, such as ">". The opposite is not true. You cannot "lead" and then close with an opening symbol, such as "<". This also applies to other paired symbols, such as ( ).
Can Use Closing Symbol for Opening
```
SELECT q'>All the king's horses>'   
 FROM  DUAL
```
```
Q'>ALLTHEKING'SHORSES>'
All the king's horses
```

Q'#ALLTHEKING'SHORSES#'
All the king's horses

Q'>ALLTHEKING'SHORSES>'
All the king's horses

Statement 12

SELECT q'>All the king's horses<'  
 FROM  DUAL

ORA-01756: quoted string not properly terminated

More Details: https://docs.oracle.com/error-help/db/ora-01756

Statement 13
Can't Use Opening Symbol for Closing
```
SELECT q'<All the king's horses<'  
 FROM  DUAL
```
```
ORA-01756: quoted string not properly terminated
```
More Details: https://docs.oracle.com/error-help/db/ora-01756
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 king's horsesP');   
END; 
```
```
Statement processed.
All the king's horses
```

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_dogs_or_cats VARCHAR2(5) := 'cats';   
BEGIN   
   EXECUTE IMMEDIATE   
     q'|BEGIN   
           dbms_output.put_line('All the king''s |'||l_dogs_or_cats||q'|');   
        END;   
      |';   
END;

Statement processed.
All the king's cats

Statement 16

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

Statement processed.
'Hello,' said the child, who didn't like goodbyes.

Statement 17

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

Statement processed.
'Hello,' said the child, who didn't like goodbyes.

Statement 18

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

Statement processed.
'Hello,' said the child, who didn't like goodbyes.

Statement 19

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

Statement processed.
'Hello,' said the child, who didn't like goodbyes.

Additional Information

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