ORA-01756 Quoted String Not Properly Terminated - Resolution Guide

ORA-01756: Quoted String Not Properly Terminated

Error Overview

Error Text: ORA-01756: quoted string not properly terminated

This error occurs when a string literal in SQL or PL/SQL has an opening single quote without a matching closing quote. It’s one of the most frequently encountered syntax errors, especially when building dynamic SQL, handling user input containing quotes, or working with text that includes apostrophes.

Common Causes

1. Missing Closing Quote

• Simple typo: forgot the trailing single quote
• String spans multiple lines without proper continuation

2. Embedded Single Quotes Not Escaped

• Data containing apostrophes (O’Brien, it’s, don’t)
• File paths with quotes
• User input with special characters

3. Dynamic SQL Construction Errors

• Incorrect quote doubling in PL/SQL
• EXECUTE IMMEDIATE string building mistakes
• Nested quotes in generated SQL

4. Copy-Paste Issues

• Smart quotes (curly quotes) from Word/email
• Different Unicode quote characters
• Hidden characters in pasted SQL

Diagnostic Examples

Example 1: Missing Closing Quote

-- ERROR: Missing closing quote
SELECT * FROM employees WHERE name = 'John Smith;
-- ORA-01756: quoted string not properly terminated

-- FIX: Add closing quote
SELECT * FROM employees WHERE name = 'John Smith';

Example 2: Embedded Apostrophe

-- ERROR: Apostrophe in O'Brien terminates the string early
SELECT * FROM employees WHERE last_name = 'O'Brien';
-- ORA-01756: quoted string not properly terminated

-- FIX Option 1: Double the single quote
SELECT * FROM employees WHERE last_name = 'O''Brien';

-- FIX Option 2: Use Q-quoting syntax (Oracle 10g+)
SELECT * FROM employees WHERE last_name = Q'[O'Brien]';
SELECT * FROM employees WHERE last_name = q'{O'Brien}';

Example 3: Dynamic SQL Quote Nightmare

-- ERROR: Complex nested quoting
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'INSERT INTO log_table VALUES ('It's a test')';
  EXECUTE IMMEDIATE v_sql;
END;
-- ORA-01756

-- FIX: Proper quote escaping
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  -- Method 1: Double all inner quotes
  v_sql := 'INSERT INTO log_table VALUES (''It''s a test'')';
  EXECUTE IMMEDIATE v_sql;

  -- Method 2: Use bind variables (PREFERRED)
  v_sql := 'INSERT INTO log_table VALUES (:val)';
  EXECUTE IMMEDIATE v_sql USING 'It''s a test';

  -- Method 3: Q-quoting in PL/SQL
  v_sql := Q'[INSERT INTO log_table VALUES ('It''s a test')]';
  EXECUTE IMMEDIATE v_sql;
END;
/

Example 4: Smart Quotes from Copy-Paste

-- ERROR: Curly/smart quotes from Word or email
SELECT * FROM employees WHERE name = \u2018John Smith\u2019;
-- ORA-01756: quoted string not properly terminated

-- FIX: Replace with standard ASCII single quotes
SELECT * FROM employees WHERE name = 'John Smith';

Oracle Q-Quoting Syntax

The alternative quoting mechanism (Q-quoting) eliminates the need to escape single quotes inside strings.

-- Syntax: Q'<delimiter>string<delimiter>'
-- Where delimiter can be: [] {} () <> or any character

-- Standard examples
SELECT Q'[Oracle's database]' FROM dual;        -- Oracle's database
SELECT Q'{It's a "test"}' FROM dual;            -- It's a "test"
SELECT Q'(Don't stop)' FROM dual;               -- Don't stop
SELECT q'!That's what she said!!' FROM dual;     -- That's what she said!

-- Useful in dynamic SQL
EXECUTE IMMEDIATE Q'[
  INSERT INTO messages (text) VALUES ('It''s working')
]';

-- In PL/SQL
DECLARE
  v_name VARCHAR2(100) := Q'[O'Brien]';
  v_msg  VARCHAR2(200) := Q'{He said "it's fine"}';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_name);  -- O'Brien
  DBMS_OUTPUT.PUT_LINE(v_msg);   -- He said "it's fine"
END;
/

Resolution Steps

Step 1: Find the Unmatched Quote

-- Count single quotes in your SQL - should be even
-- In your text editor, search for ' and check each pair

-- Common pattern: look for strings containing apostrophes
-- Names: O'Brien, O'Connor, D'Angelo
-- Contractions: it's, don't, can't, won't
-- Possessives: employee's, company's

Step 2: Apply the Correct Fix

-- For static SQL: double the quote
WHERE name = 'O''Brien'

-- For dynamic SQL: use bind variables
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE name = :n' USING v_name;

-- For complex strings: use Q-quoting
v_sql := Q'[SELECT * FROM emp WHERE name = 'O''Brien']';

Step 3: Handle User Input Safely

-- NEVER concatenate user input directly (SQL injection risk!)
-- BAD:
v_sql := 'SELECT * FROM emp WHERE name = ''' || v_user_input || '''';

-- GOOD: Use bind variables
v_sql := 'SELECT * FROM emp WHERE name = :name';
EXECUTE IMMEDIATE v_sql USING v_user_input;

-- If you MUST concatenate, use REPLACE to escape
v_safe_input := REPLACE(v_user_input, '''', '''''');

Prevention Strategies

1. Always Use Bind Variables

-- Bind variables eliminate quoting issues entirely
-- They also prevent SQL injection and improve performance

-- PL/SQL
EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:1, :2)' USING v_id, v_name;

-- JDBC/Application code
PreparedStatement ps = conn.prepareStatement("SELECT * FROM emp WHERE name = ?");
ps.setString(1, "O'Brien");

2. Use Q-Quoting for Complex Strings

-- Adopt Q-quoting as a standard practice for strings with quotes
v_html := Q'[<div class="container">It's a <span class='highlight'>test</span></div>]';

3. Input Sanitization Function

-- Create a reusable function for safe string handling
CREATE OR REPLACE FUNCTION safe_string(p_input VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
  RETURN REPLACE(p_input, '''', '''''');
END;
/

4. IDE Quote Highlighting

• Use SQL Developer, DataGrip, or VS Code with Oracle extensions
• These editors highlight unmatched quotes in real-time
• Enable “rainbow brackets” for visual quote matching

Related Errors

• ORA-00911: Invalid Character - Special characters in SQL
• ORA-00933: SQL Command Not Properly Ended - Trailing content after valid SQL
• ORA-06550: PL/SQL Compilation Error - String issues in PL/SQL blocks
• ORA-00923: FROM Keyword Not Found - Syntax errors from unmatched quotes