Quoting string literal technique for single-quotation marks

Tags

dynamic sql single quotes, q'[]', q-quote, q-quoting, Quoting string literal, quoting string literal mechanism, quoting string literal technique, single quotes

I have seen many questions on the Oracle forums on how to avoid the error due to single-quotation marks in the string literal. One such question was http://stackoverflow.com/questions/27373158/how-to-anticipate-and-escape-in-oracle/27373394#27373394 in stack Overflow.

Oracle SQL doesn’t allow single-quotes within a string literal itself enclosed within single-quotes. So, what’s the workaround? Usually, before introduction of 10g, developers used to embed the single-quotes using two single-quotes around it.

However, the new feature of quoting string literals makes the life of developers far easy than before.

Let’s look at the Quoting string literal technique. The syntax is q'[…]’, where the “[“ and “]” characters can be any of the following as long as they do not already appear in the string.

• !
• [ ]
• { }
• ( )
• < >

You don’t have to worry about the single-quotation marks within the string.

Let’s look at an example.

Suppose we have a column value as abc’de, so you could simply write the SQL as,

SELECT q'[abc'de]' FROM DUAL;

Another example using an anonymous PL/SQL block :-

SQL> set serveroutput on
SQL> DECLARE
  2    v VARCHAR2(1024);
  3  BEGIN
  4    v := q'[It's a ' single quote]';
  5    DBMS_OUTPUT.PUT_LINE(v);
  6  END;
  7  /
It's a ' single quote

PL/SQL procedure successfully completed.

SQL>

Let’s see how it makes working with dynamic SQL so easy :-

SQL> set serveroutput on
SQL> DECLARE
  2    var_sql VARCHAR2(2000);
  3    var_empno NUMBER;
  4  BEGIN
  5    var_sql := q'[SELECT empno FROM emp WHERE ename = 'SCOTT']';
  6    EXECUTE IMMEDIATE var_sql INTO var_empno;
  7    DBMS_OUTPUT.PUT_LINE( 'Employee number of SCOTT is '|| var_empno );
  8  END;
  9  /
Employee number of SCOTT is 7788

PL/SQL procedure successfully completed.

SQL>

It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.

Hope it helps!