Oracle SQL Quote Operator

Sometimes we use single quotation mark in our sentence. This quotation mark delimiter is specified by quote operator. So quote(q) operator is used in SQL statements to show single quotation mark. Quote operator is not supported in 9i. This operator will support for 10g or higher.


For using the quote (q) operator, We can use single or multi byte delimiter or any of the following character pairs:

–> []
–> {}
–> ()
–> <>


Syntax of quote (q) operator:

q'[text to be quoted]’



First of all, I am writing a sentence in SQL statement with single quotation mark and with out quote (q) operator below:

SELECT 'I'm using quote operator in SQL statement'
FROM dual;


After executing the above query, I am getting below error from oracle database.

ORA-01756: quoted string not properly terminated


Now, I am writing same sentence again in SQL statement with quote (q) operator below:

SELECT q'[I'm using quote operator in SQL statement]' "Quote (q) Operator"
FROM dual;

Here, I used “Quote (q) Operator” for aliases. Above statement show the below result:

Quote (q) Operator
I'm using quote operator in SQL statement


We can use any character as single quotation mark delimiter.


SQL statement:

SELECT q'{Oracle's quote operator in SQL}' "Quote Operator"
FROM dual;


Quote Operator
Oracle's quote operator in SQL


SQL statement:

SELECT q'XThis is my Blog. It's personal.X' "Quote Operator"
FROM dual;


Quote Operator
This is my Blog. It's personal.


SQL statement:

SELECT first_name||' '||q'<Employee's Manager id: >'|| manager_id "Q Operator"
FROM employees
WHERE department_id = 50;


Q Operator
Douglas Employee's Manager id: 124
Matthew Employee's Manager id: 100
Adam Employee's Manager id: 100
Payam Employee's Manager id: 100
Shanta Employee's Manager id: 100
Kevin Employee's Manager id: 100
Julia Employee's Manager id: 120
Irene Employee's Manager id: 120
James Employee's Manager id: 120
Steven Employee's Manager id: 120


SQL statement:

SELECT last_name ||' '||q'+Department's ID : +'||department_id "Dept ID"
FROM employees
WHERE manager_id = 100;


Dept ID
Hartstein Department's ID : 20
Kochhar Department's ID : 90
De Haan Department's ID : 90
Raphaely Department's ID : 30
Weiss Department's ID : 50
Fripp Department's ID : 50
Kaufling Department's ID : 50
Vollman Department's ID : 50
Mourgos Department's ID : 50
Russell Department's ID : 80
Partners Department's ID : 80