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?
Possibly related:
- 3 Useful SQL*Plus Tips
- 5 Recent Oracle Security Related Documents
- How to personalize forms in Oracle Applications
- I don’t understand that internet jive
- About The BUILTIN, FIPSFLAG and INTERFACE Pragmas in Oracle
Tagged function, sql | Post a Comment
Hope everything is working out...
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…
March 30th, 2006, at 5:18 am #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.
April 1st, 2006, at 4:21 pm #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.’>’
March 27th, 2007, at 7:35 am #FROM DUAL;
not sure why but my single-quotes in the previuos comment have been turned into back and forward quotes?
March 27th, 2007, at 7:38 am #Simon, using SQL*Plus, the following worked fine:
However, it failed when I ran it in SQL Navigator.
March 27th, 2007, at 8:55 am #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.
March 30th, 2007, at 7:31 am #There is a bug 2489201 but it does not appear to be publicly accessible.
March 30th, 2007, at 8:15 am #Simon, thanks for the update. I have been bitten by the “Oracle home” syndrome before.
March 30th, 2007, at 9:32 am #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
:
September 5th, 2007, at 11:50 pm #Chander, thanks for posting an example. The Q-quote mechanism sure does make a programmer’s life easier in many cases.
September 6th, 2007, at 7:35 am #