quoting string literals in 10g

http://www.oracle-developer.net/display.php?id=311

This short article introduces Oracle's new quoting mechanism in PL/SQL. This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This is particularly useful for building dynamic SQL statements that contain quoted literals.

The mechanism is invoked with a simple "q" in PL/SQL only. 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.

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

Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer).

a simple example

The following example demonstrates how simple it is to use this mechanism when building a string that contains single quotes.

SQL> DECLARE
  2     v VARCHAR2(1024);
  3  BEGIN
  4     v := q'[It's a string with embedded quotes...]';
  5     DBMS_OUTPUT.PUT_LINE(v);
  6  END;
  7  /
It's a string with embedded quotes...

PL/SQL procedure successfully completed.

using the quoting mechanism in dynamic sql

Many developers will be familiar with dynamic SQL. While providing a flexible solution to many programming problems, dynamic SQL can also be difficult to build, debug and support. The quoting mechanism doesn't make these problems go away, but it at least makes dynamic SQL a little bit easier to transfer between SQL editors and packages during development.

The following is a contrived example of a dynamic SQL statement that includes literal quotes in the string. The sharp-witted amongst us will notice that this statement doesn't in fact need to be dynamic (and even if it did, it should be using bind variables). However, in the interest of demonstrating the quoting mechanism in as simple and short an example as possible, we'll stick with it.

SQL> DECLARE
  2     v_sql VARCHAR2(1024);
  3     v_cnt PLS_INTEGER;
  4  BEGIN
  5     v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE']';
  6     EXECUTE IMMEDIATE v_sql INTO v_cnt;
  7     DBMS_OUTPUT.PUT_LINE(
  8        TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.'
  9        );
 10  END;
 11  /
4 tables in USER_OBJECTS.

PL/SQL procedure successfully completed.