DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01489 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01489: Result of String Concatenation Is Too Long

ORA-01489 is thrown by Oracle when the result of a string concatenation (||) operation exceeds the maximum allowed length of the target data type — 4,000 bytes for VARCHAR2 in SQL, or 32,767 bytes in PL/SQL. This error is especially common in reporting queries, ETL pipelines, and dynamic SQL generation where multiple long columns are combined into a single string. If left unaddressed, it can silently lurk in development (where test data is short) and explode in production.

Top 3 Causes

1. Concatenating Multiple Long VARCHAR2 Columns in SQL

Joining several text-heavy columns with || easily pushes the result past 4,000 bytes.

-- This can raise ORA-01489 if combined length exceeds 4,000 bytes
SELECT first_name || ' ' || address || ' ' || description || ' ' || notes
FROM customer_profiles;

-- Fix: Cast the first operand to CLOB
SELECT TO_CLOB(first_name) || ' ' || address || ' ' || description || ' ' || notes
FROM customer_profiles;
Enter fullscreen mode Exit fullscreen mode

2. Accumulating Strings in a PL/SQL Loop

Dynamically building SQL or text inside a loop using a VARCHAR2 variable is a classic trap.

-- DANGEROUS: VARCHAR2 hits 32,767 byte wall inside PL/SQL
DECLARE
  v_sql VARCHAR2(32767);
BEGIN
  v_sql := 'SELECT * FROM orders WHERE status IN (';
  FOR r IN (SELECT status_code FROM status_master) LOOP
    v_sql := v_sql || '''' || r.status_code || ''',';
  END LOOP;
  EXECUTE IMMEDIATE RTRIM(v_sql, ',') || ')';
END;
/

-- SAFE: Use a CLOB variable instead
DECLARE
  v_sql CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_sql, TRUE);
  DBMS_LOB.APPEND(v_sql, 'SELECT * FROM orders WHERE status IN (');
  FOR r IN (SELECT status_code FROM status_master) LOOP
    DBMS_LOB.APPEND(v_sql, '''' || r.status_code || ''',');
  END LOOP;
  v_sql := SUBSTR(v_sql, 1, DBMS_LOB.GETLENGTH(v_sql)-1) || ')';
  EXECUTE IMMEDIATE v_sql;
  DBMS_LOB.FREETEMPORARY(v_sql);
END;
/
Enter fullscreen mode Exit fullscreen mode

3. LISTAGG Overflow on High-Cardinality Groups

LISTAGG returns VARCHAR2, so aggregating many values in one group will breach the 4,000-byte limit.

-- May cause ORA-01489 with large groups
SELECT dept_id,
       LISTAGG(emp_name, ', ') WITHIN GROUP (ORDER BY emp_name) AS names
FROM employees
GROUP BY dept_id;

-- Fix 1 (Oracle 12c+): Use XMLAGG for CLOB output
SELECT dept_id,
       RTRIM(XMLAGG(XMLELEMENT(e, emp_name || ', ')
             ORDER BY emp_name).GETCLOBVAL(), ', ') AS names
FROM employees
GROUP BY dept_id;

-- Fix 2 (Oracle 19c+): ON OVERFLOW TRUNCATE clause
SELECT dept_id,
       LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
         WITHIN GROUP (ORDER BY emp_name) AS names
FROM employees
GROUP BY dept_id;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
SQL concatenation too long Wrap first operand with TO_CLOB()
PL/SQL string accumulation Switch VARCHAR2 variable to CLOB
LISTAGG overflow Use XMLAGG or ON OVERFLOW TRUNCATE
Must truncate intentionally Use SUBSTR(..., 1, 4000) 
-- Universal safe pattern: always CLOB-cast before heavy concatenation
SELECT TO_CLOB(col_a) || col_b || col_c || col_d AS safe_result
FROM your_table;

-- Check string length before concatenating
SELECT CASE
         WHEN LENGTH(col_a) + LENGTH(col_b) > 3990
         THEN SUBSTR(col_a || col_b, 1, 3990) || '...'
         ELSE col_a || col_b
       END AS guarded_result
FROM your_table;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Design with CLOB from the start. If any column or derived expression could realistically exceed 4,000 bytes at scale, define it as CLOB during data modeling — not after the error appears in production. Add a concatenation length check to your code review checklist.

2. Always test with boundary-length data. Populate test datasets with strings at their maximum defined length. Automate this as part of your CI/CD pipeline so that ORA-01489 is caught before it ever reaches production. Pair this with periodic monitoring of actual column length distributions in your production database.

Related Errors

  • ORA-06502 — PL/SQL variable too small to hold the assigned value; often appears alongside ORA-01489.
  • ORA-01704 — String literal in SQL exceeds 4,000 bytes.
  • ORA-22835 — CLOB-to-VARCHAR2 implicit conversion overflow.

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