msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

The Q-quote mechanism

In Oracle, you quote string literals in single quotes. If you have single quotes inside that string literal, you have to escape each single quote with another single quote. But, did you know that, in 10gR2, there is an alternative approach, called the “Q-quote mechanism”?. I only knew about it when Martin mentioned it on OraQA.

Examples of the classic way:

SQL> select 'name LIKE ''%DBMS_%%'''
  2  from dual
  3  /

'NAMELIKE''%DBMS_%%'
--------------------
name LIKE '%DBMS_%%'

SQL> select 'Data,'' he said, ''Make it so.'''
  2  from dual
  3  /

'DATA,''HESAID,''MAKEITSO.'''
-----------------------------
Data,' he said, 'Make it so.'

SQL> select 'name like ''['''
  2  from dual
  3  /

'NAMELIKE''['
-------------
name like '['

From the Oracle documentation: The Q-quote mechanism, enables you to specify q or Q followed by a single quote and then another character to be used as the quote delimiter. The Q-quote delimiter can be any single- or multibyte character except space, tab, and return. If the opening quote delimiter is a [, {, <, or ( character, then the closing quote delimiter must be the corresponding ], }, >, or ) character. In all other cases, the opening and closing delimiter must be the identical character.

For example:

SQL> select q'(name LIKE '%DBMS_%%')'
  2  from dual
  3  /

Q'(NAMELIKE'%DBMS_%%
--------------------
name LIKE '%DBMS_%%'

SQL> select q'<'Data,' he said, 'Make it so.'>'
  2  from dual
  3  /

Q'<'DATA,'HESAID,'MAKEITSO.'>'
------------------------------
'Data,' he said, 'Make it so.'

SQL> select q'"name like '['"'
  2  from dual
  3  /

Q'"NAMELIKE'[
-------------
name like '['

Now, why would someone use this new q-quote mechanism instead of the classic one? Is it more readable to you when you want to escape single quotes inside a string literal?


Filed in Oracle, Tips on 30 Mar 06 | Tags: ,


Reader's Comments

  1. |

    For a simple example like this it hardly matters, but when you stat writing scripts that generate scripts, you can easily end up with loads of quotes, making it practically unreadable. In this situation, it can come in handy.

    Cheers

    Tim…

  2. |

    So, the reason Oracle introduced this q-quote mechanism is to make string literals, containing single quotes, more readable, especially when escaping with the traditional approach results in a hard to read strings.

  3. |

    However an odd number of single-quotes within the string makes it fail. Am I missing something here?

    SELECT q'<‘Data, he said, ‘Make it so.’>’ FROM DUAL;

  4. |

    not sure why but my single-quotes in the previuos comment have been turned into back and forward quotes?

  5. |

    Simon, using SQL*Plus, the following worked fine:

    SQL> connect hr/hr
    Connected.
    SQL> SELECT q'<'Data, he said, 'Make it so.'>'
      2   FROM DUAL
      3  /
    
    Q'<'DATA,HESAID,'MAKEITSO.'>'
    -----------------------------
    'Data, he said, 'Make it so.'
    

    However, it failed when I ran it in SQL Navigator.

  6. |

    Turns out that it’s a 9i and earlier bug, so it depends on which Oracle Home you use to connect to your DB.

    Our databases are all 10g but our Oracle client software ranges from 8.0 through to 10g R2. The guy from Oracle support said something along the lines of ” … as you know the parser used by the database matches the version of the oracle client used to connect.”

    As we don’t actually use any 9i clients we couldn’t provide a business case for raising a bug.

  7. |

    There is a bug 2489201 but it does not appear to be publicly accessible.

  8. |

    Simon, thanks for the update. I have been bitten by the “Oracle home” syndrome before.

  9. |

    The q function has solved a problem that I’ve struggled to resolve, where a script prompts for a value (that may include 1 or more quotes e.g. a where clause) and I have to manipulate that value before I can use it. In this case the value is spooled out as part of a select to a file that will be ‘started’.

    E.g.

    var v_where_clause VARCHAR2(1024);

    ACCEPT a_Where PROMPT ‘ Enter a valid WHERE clause: ‘

    BEGIN — :v_where_clause := Q’[&a_Where]‘; :v_where_clause := RTRIM(:v_where_clause, ‘ ;’); IF LTRIM(UPPER(:v_where_clause)) LIKE ‘WHERE%’ THEN — Clip the ‘Where’ clause from the point after the ‘WHERE ‘ (if included) :v_where_clause := SUBSTR(:v_where_clause, INSTR(UPPER(:v_where_clause), ‘WHERE ‘) + 6); END IF; — END; /

    Prior to this, the where clause had to use double-quotes (or pairs of single-quotes) around literals, as I couldn’t ‘capture’ the value in a variable.

    The whole script follows. Needless to say this is less useful with modern IDE tools, but in our Live environment it has proved invaluabl.

    SET ECHO OFF

    PROMPT vL2007/09/05.1 PROMPT 1) When prompted, enter the name of the TABLE for selection. PROMPT 2) You may then add a simple WHERE clause, or to default. PROMPT 3) You may then add a simple ORDER BY clause, or to default. PROMPT 4) Note: DATE values should use the format DD-MON-YYYY. PROMPT

    SET HEADING OFF FEEDBACK OFF VERIFY OFF NUMWIDTH 12 TRIMSPOOL ON PAGESIZE 0 LINESIZE 200

    ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY';

    var v_table_name VARCHAR2(100); var v_owner VARCHAR2(30); var v_where_clause VARCHAR2(1024); var v_order_by VARCHAR2(1024); var v_dot_posn NUMBER;

    ACCEPT a_Tab PROMPT ‘ Enter the name of the required TABLE: ‘ ACCEPT a_Where PROMPT ‘ Enter a valid WHERE clause: ‘ ACCEPT a_Order PROMPT ‘ Enter a valid ORDER BY clause: ‘

    BEGIN — :v_table_name := NVL(‘&a_Tab’, ‘Dual’); :v_dot_posn := INSTR(:v_table_name, ‘.’); — IF NVL(:v_dot_posn, 0) > 0 THEN :v_owner := SUBSTR(:v_table_name, 1, (:v_dot_posn – 1)); :v_table_name := SUBSTR(:v_table_name, (:v_dot_posn + 1)); ELSE :v_owner := USER; — or whatever defaulkt is required END IF; — :v_where_clause := Q’[&a_Where]‘; :v_where_clause := RTRIM(:v_where_clause, ‘ ;’); IF LTRIM(UPPER(:v_where_clause)) LIKE ‘WHERE%’ THEN — Clip the ‘Where’ clause from the point after the ‘WHERE ‘ (if included) :v_where_clause := SUBSTR(:v_where_clause, INSTR(UPPER(:v_where_clause), ‘WHERE ‘) + 6); END IF; — :v_order_by := Q’[&a_Order]‘; :v_order_by := RTRIM(:v_order_by, ‘ ;’); IF LTRIM(UPPER(:v_order_by)) LIKE ‘ORDER %BY %’ THEN — Clip the ‘Order By’ clause from the point after the ‘ BY ‘ (if included) :v_order_by := SUBSTR(:v_order_by, INSTR(UPPER(:v_order_by), ‘ BY ‘) + 4); END IF; — END; /

    SET TERMOUT OFF

    SPOOL TabSpool.sql

    SELECT ‘SET ECHO OFF’ || CHR(10) || ‘SET HEADING OFF FEEDBACK OFF PAGESIZE 0′ || CHR(10) || ‘SET LINESIZE 70 ARRAYSIZE 5′ || CHR(10) || ‘ALTER SESSION ENABLE PARALLEL DML;’ || CHR(10) || ‘PROMPT’ FROM Dual;

    SELECT ‘SELECT /*+ APPEND PARALLEL(‘ || :v_table_name || ‘, 7) */’ FROM Dual;

    SELECT ”” || LPAD(RPAD(column_name, 30), 35) || ‘= [' || ''' || ' || column_name || ' || '']”,’ FROM all_tab_columns WHERE table_name = UPPER(:v_table_name) AND data_type IN (‘CHAR’, ‘VARCHAR2′, ‘NUMBER’, ‘DATE’) AND owner = UPPER(:v_owner) ORDER BY column_id ASC;

    SELECT ”’ ” FROM ‘ || :v_owner || ‘.’ || :v_table_name || ‘ WHERE ‘ || NVL(:v_where_clause, ‘1=1′) || DECODE(:v_order_by, NULL, NULL, ‘ ORDER BY ‘ || :v_order_by) || ‘;’ FROM Dual;

    SELECT ‘SELECT CHR(32) FROM Dual;’ FROM Dual;

    SELECT ‘SET HEADING ON FEEDBACK ON PAGESIZE 100 LINESIZE 120 ECHO ON ‘ || RPAD(‘ ‘,60) FROM Dual;

    SPOOL OFF

    UNDEFINE a_Tab UNDEFINE a_Where UNDEFINE a_Order

    SET HEADING ON FEEDBACK ON VERIFY ON ARRAYSIZE 20 TRIMSPOOL OFF PAGESIZE 100 LINESIZE 120 TERMOUT ON ECHO ON

    START TabSpool.sql

    SET ECHO OFF

    :

  10. |

    Chander, thanks for posting an example. The Q-quote mechanism sure does make a programmer’s life easier in many cases.

  11. |

    Just supporting ‘This is a \’quoted\’ string which even contains newlines\ncarriagereturns\rand backslashes\!!!’ would have been to easy……..

    And it would have been to standards and widespread too…..

    But I understand the problem. The -quotes started to appear in C and database existed befor that…

    nomike