Avoid the hassle of escaping quotes with user-defined quotes

You’re probably quite familiar with the practice of escaping quotes and other special characters in code. But it can sure be a pain if you or your program has to insert lengthy segments of text. Fortunately, Oracle10g eliminated the necessity of escaping quotes in SQL statements by introducing the ability to have user-defined quote characters.

Prior to Oracle10g, if you wanted to include quotes in text, you had to escape the quote with another quote such as:

SELECT 'Pinnacle's p's and q's text' from dual;

With Oracle10g, you can rewrite this as:

SELECT Q'$Pinnacle's p's and q's text$' from dual;

Note that the quoted strings starts with the letter Q, followed by a single quote and the new quote character. It ends with the new quote character and a single quote. We used a dollar sign ($) as our quote character, but you can use other characters if you’d like. Now you can put any quoted text in between your quote characters.

You can use this feature in PL/SQL as well, like this:

CREATE or REPLACE PROCEDURE proc_test (pi_name VARCHAR2)
IS
begin
   DBMS_OUTPUT.PUT_LINE('Name is: '||pi_name);
end;
/

Then invoke the procedure in an anonymous block such as:

DECLARE
l_var varchar2(100):= Q'$Pinnacle's p's and q's text$';
BEGIN
   proc_test(l_var);
END;
/

The output is:

Name is: Pinnacle's p's and q's text