Alternative Quoting Mechanism (”Q”) for String Literals

In PL/SQL, handling long text with single quotes can be tricky. Oracle provides the q[] mechanism for easier string literal management. By using custom delimiters, you can avoid escaping single quotes.

All know that is sth. Problem with long text in PL/SQL. It is good to start use q[ mechanism.

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.

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


BEGIN  
    DBMS_OUTPUT.PUT_LINE (  
       q'[What's a quote among friends?]');  
END;