q Function Inside a q Function:

Reference: http://www.talkapex.com/2009/03/q-function-escape-single-quotes.html 

Last month I wrote about how to escape single quotes in string using the q function:

I recently had an requirement where I wanted to use a q function inside another q function. Here's what I tried:

DECLARE
  l_code varchar2(4000);

BEGIN
  l_code := q'!BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END; !';
  
  dbms_output.put_line(l_code);
  
  execute immediate l_code;
  
END;
/

ERROR:
ORA-01756: quoted string not properly terminated
You'll notice that the above code doesn't run. The reason it doesn't work is that I'm using the same character (!) as the quote delimiter. To fix this, each "Q" block must have it's own unique quote delimiter. Below is a copy of the same code but using two different quote delimiters (# and !).
   
DECLARE
  l_code varchar2(4000);

BEGIN
  l_code := q'#BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END; #';
  
  dbms_output.put_line(l_code);
  
  execute immediate l_code;
  
END;
/

BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END;
It's cold in Calgary

PL/SQL procedure successfully completed.
For more information on the q function (it's real name is "Q-quote mechanism") read the Oracle documentation: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#BABECADE and scroll down to the "Quoting Character Literals" section.