Oracle ORA-01704 Error: Causes and Solutions Complete Guide

ORA-01704: String Literal Too Long — Causes, Fixes, and Prevention

ORA-01704 is thrown by Oracle Database when a string literal enclosed in single quotes (') within a SQL statement exceeds 4,000 bytes in length. This is a hard parser-level limit, meaning Oracle cannot even begin to process the statement before rejecting it. It commonly surfaces during data migrations, automated SQL script generation, or when developers attempt to hard-code large text values directly into DML statements.

---

Top 3 Causes

1. Hard-coded Long Strings in INSERT/UPDATE Statements

The most common cause. A developer or script tries to insert a large block of text (e.g., HTML content, JSON, or log data) directly as a SQL literal.

-- This will throw ORA-01704 if the string exceeds 4,000 bytes
INSERT INTO documents (doc_id, body)
VALUES (1, 'This is an extremely long string that goes well beyond
            four thousand bytes in total length ... [truncated]');

2. Auto-generated Migration Scripts with CLOB Data

ETL tools or export utilities often generate SQL scripts that wrap CLOB column values in single quotes without checking their length. Any exported value longer than 4,000 bytes will cause this error upon re-import.

-- Auto-generated script (problematic pattern)
INSERT INTO legacy_data (id, description)
VALUES (42, '[AUTO-EXPORTED TEXT EXCEEDING 4000 BYTES ...]');
-- ORA-01704: string literal too long

3. Dynamic SQL Construction in PL/SQL

When building dynamic SQL strings in PL/SQL, individual literal segments — not just the concatenated result — must each be under 4,000 bytes. Passing a long constant value into EXECUTE IMMEDIATE will still trigger ORA-01704.

DECLARE
    v_sql VARCHAR2(32767);
BEGIN
    -- Even though v_sql can hold more, the literal itself is too long
    v_sql := 'INSERT INTO logs (msg) VALUES (''' ||
             '[String literal segment exceeding 4000 bytes here...]' ||
             ''')';
    EXECUTE IMMEDIATE v_sql; -- ORA-01704 triggered by the literal
END;
/

---

Quick Fix Solutions

Fix 1: Use TO_CLOB() with Concatenation

Split the long string into chunks under 4,000 bytes each and join them with TO_CLOB().

INSERT INTO documents (doc_id, body)
VALUES (
    1,
    TO_CLOB('First chunk of text under 4000 bytes...') ||
    TO_CLOB('Second chunk of text under 4000 bytes...') ||
    TO_CLOB('Third chunk of text under 4000 bytes...')
);
COMMIT;

Fix 2: Use a PL/SQL CLOB Variable

Assign data to a CLOB variable in PL/SQL and bind it in the DML — no literal length limit applies to variable assignments.

DECLARE
    v_content CLOB;
BEGIN
    v_content := 'First portion of a very long text...';
    v_content := v_content || 'Second portion appended safely...';
    v_content := v_content || 'Third portion appended safely...';

    INSERT INTO documents (doc_id, body)
    VALUES (2, v_content);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted: ' || DBMS_LOB.GETLENGTH(v_content) || ' bytes');
END;
/

Fix 3: Use DBMS_LOB.WRITEAPPEND for Large Data

For very large payloads, write directly to the LOB locator using the DBMS_LOB package.

DECLARE
    v_clob  CLOB;
    v_part1 VARCHAR2(4000) := 'First data chunk...';
    v_part2 VARCHAR2(4000) := 'Second data chunk...';
BEGIN
    INSERT INTO documents (doc_id, body)
    VALUES (3, EMPTY_CLOB())
    RETURNING body INTO v_clob;

    DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_part1), v_part1);
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_part2), v_part2);
    DBMS_LOB.CLOSE(v_clob);

    COMMIT;
END;
/

---

Prevention Tips

• Design columns correctly from the start: Use CLOB or NCLOB for any column that may store content longer than 4,000 bytes (articles, logs, XML, JSON). Don't assume VARCHAR2(4000) is sufficient.
• Always use bind variables in application code: Using PreparedStatement (Java), parameterized queries (Python/cx_Oracle), or :bind_variable syntax in SQL completely avoids string literal length restrictions and also improves performance through cursor sharing.
• Add length validation in migration scripts: Any script that auto-generates SQL from data should check string lengths and automatically apply TO_CLOB() chunking when values exceed 4,000 bytes.

---

Related Errors

Error Code	Description
ORA-06502	PL/SQL value error — VARCHAR2 buffer too small
ORA-22835	CLOB to CHAR conversion buffer overflow
ORA-00910	Specified length too long for datatype (DDL)

---

📖 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.