DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01756 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01756: quoted string not properly terminated

ORA-01756 is a SQL parsing error thrown by Oracle when it encounters a string literal that starts with a single quote but never finds the matching closing quote. This typically happens when single quotes inside string values are not properly escaped, causing the Oracle parser to misinterpret where the string ends. It is one of the most common errors when building dynamic SQL or inserting data that contains apostrophes.

Top 3 Causes

1. Unescaped Single Quote Inside a String Literal

The most frequent cause is embedding an apostrophe (e.g., O'Brien, McDonald's) directly into a SQL string without escaping it.

-- WRONG: causes ORA-01756
SELECT * FROM employees WHERE last_name = 'O'Brien';

-- CORRECT: escape with double single-quotes
SELECT * FROM employees WHERE last_name = 'O''Brien';

-- BETTER (Oracle 10g+): use q-quote syntax
SELECT * FROM employees WHERE last_name = q'[O'Brien]';

INSERT INTO customers (name) VALUES (q'[McDonald's]');
Enter fullscreen mode Exit fullscreen mode

2. Dynamic SQL Built by String Concatenation

When applications (Java, Python, PHP, PL/SQL) construct SQL by concatenating user input directly into the query string, any quote character in the input breaks the SQL syntax.

-- DANGEROUS dynamic SQL in PL/SQL (string concat)
DECLARE
  v_name VARCHAR2(100) := 'O''Brien';
  v_sql  VARCHAR2(500);
  v_count NUMBER;
BEGIN
  -- This approach is fragile and risks ORA-01756
  v_sql := 'SELECT COUNT(*) FROM employees '
        || 'WHERE last_name = ''' || v_name || '''';
  EXECUTE IMMEDIATE v_sql INTO v_count;
END;
/

-- SAFE: use bind variables with EXECUTE IMMEDIATE
DECLARE
  v_name  VARCHAR2(100) := 'O''Brien';
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees WHERE last_name = :1'
    INTO v_count
    USING v_name;
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_count);
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Smart Quotes or Invisible Unicode Characters

Copying SQL from a web browser, Word document, or certain IDEs can silently replace standard ASCII single quotes (', code 39) with Unicode "smart quotes" (' U+2018, ' U+2019). Oracle does not recognize these as string delimiters, triggering ORA-01756.

-- Diagnose: check the actual character codes with DUMP
SELECT DUMP(q'[test's value]', 1016) FROM dual;
-- A normal apostrophe should show code: Typ=96 Len=12: ... 27 ...
-- (hex 27 = decimal 39 = ASCII single quote)

-- Fix: retype the quotes manually in a plain-text editor
-- then verify the fixed SQL
SELECT * FROM employees WHERE last_name = 'O''Brien';

-- Use CHR(39) as an alternative when quote handling is tricky
SELECT 'Hello' || CHR(39) || 's World' AS result FROM dual;
-- Result: Hello's World
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Recommended Fix
Literal apostrophe in SQL Use '' (double single-quote) or q'[...]' syntax
Dynamic SQL in PL/SQL Switch to bind variables with EXECUTE IMMEDIATE ... USING
App-level dynamic SQL Use PreparedStatement (Java) / parameterized queries (Python)
Smart quote from copy-paste Retype quotes in a plain-text editor (Notepad, vi, etc.)

Prevention Tips

Always use bind variables. Whether you are writing PL/SQL or application-level code, passing values through bind variables (:param) instead of string concatenation eliminates ORA-01756 entirely for dynamic queries. It also protects against SQL Injection and improves performance through execution plan reuse.

-- Python (cx_Oracle / oracledb) example
cursor.execute(
    "SELECT * FROM employees WHERE last_name = :name",
    name="O'Brien"
)
Enter fullscreen mode Exit fullscreen mode

Adopt q-quote syntax for string literals. For any SQL script or stored procedure that includes hard-coded strings containing apostrophes, standardize on Oracle's q-quote notation (q'[...]', q'{...}', etc.). Add a linting rule or peer-review checklist item to flag raw string concatenation patterns before they reach production.

-- Easy to read, no escaping needed
INSERT INTO product_notes (note)
VALUES (q'[Customer's order can't be processed without ID]');
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01740 — Missing double quote in a quoted identifier; the double-quote equivalent of ORA-01756.
  • ORA-00907 — Missing right parenthesis; often appears alongside ORA-01756 when broken quotes corrupt the entire SQL structure.
  • ORA-06550 — PL/SQL compilation error wrapper that surfaces ORA-01756 with a line and column number for easier debugging.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)