ORA-01489 Result of String Concatenation Is Too Long - Resolution Guide

ORA-01489: Result of String Concatenation Is Too Long

Error Overview

Error Text: ORA-01489: result of string concatenation is too long

This error occurs when concatenating VARCHAR2 values produces a result exceeding 4000 bytes (or 32767 bytes in extended mode). It’s commonly encountered in reporting queries, ETL processes, data exports, and LISTAGG operations.

Common Causes

1. LISTAGG Overflow

• Aggregating many values into a single string
• No built-in length control in basic LISTAGG (pre-12.2)

2. VARCHAR2 Concatenation in SQL

• Combining multiple columns that collectively exceed 4000 bytes
• Building dynamic SQL strings
• Creating CSV-style output in queries

3. ETL and Data Migration

• Merging multiple source columns into a single target
• Transforming normalized data into denormalized strings

4. XMLAGG / JSON Generation

• Building XML or JSON strings that exceed VARCHAR2 limits

Diagnostic Queries

Find the Actual String Lengths

-- Check lengths of values being concatenated
SELECT
  MAX(LENGTH(col1)) as max_col1,
  MAX(LENGTH(col2)) as max_col2,
  MAX(LENGTH(col1) + LENGTH(col2)) as max_combined
FROM your_table;

-- For LISTAGG, check how long the result would be
SELECT grouping_column,
  SUM(LENGTH(value_column) + 1) as estimated_length,
  COUNT(*) as value_count
FROM your_table
GROUP BY grouping_column
ORDER BY estimated_length DESC;

Check MAX_STRING_SIZE

SELECT value FROM v$parameter WHERE name = 'max_string_size';
-- STANDARD = 4000 byte limit
-- EXTENDED = 32767 byte limit

Resolution Steps

Solution 1: LISTAGG with ON OVERFLOW (Oracle 12.2+)

-- ERROR: LISTAGG exceeds 4000 bytes
SELECT department_id,
  LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) as emp_list
FROM employees
GROUP BY department_id;
-- ORA-01489 when a department has many employees

-- FIX: Use ON OVERFLOW TRUNCATE (Oracle 12.2+)
SELECT department_id,
  LISTAGG(employee_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
    WITHIN GROUP (ORDER BY employee_name) as emp_list
FROM employees
GROUP BY department_id;
-- Truncates at 4000 bytes and appends "...(47)" showing omitted count

Solution 2: Use XMLAGG for Unlimited Concatenation

-- Works on all Oracle versions, returns CLOB (no size limit)
SELECT department_id,
  RTRIM(XMLAGG(XMLELEMENT(e, employee_name || ', ')
    ORDER BY employee_name).EXTRACT('//text()').getClobVal(), ', ') as emp_list
FROM employees
GROUP BY department_id;

Solution 3: CLOB Concatenation

-- Cast to CLOB before concatenating to avoid the 4000-byte limit
SELECT TO_CLOB(long_col1) || TO_CLOB(long_col2) || TO_CLOB(long_col3)
FROM your_table;

-- For INSERT operations
INSERT INTO target_table (id, combined_text)
SELECT id, TO_CLOB(col1) || ' ' || TO_CLOB(col2) || ' ' || TO_CLOB(col3)
FROM source_table;

Solution 4: PL/SQL CLOB Builder

-- For complex concatenation logic, use PL/SQL with CLOB
CREATE OR REPLACE FUNCTION build_employee_list(p_dept_id NUMBER)
RETURN CLOB
IS
  v_result CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_result, TRUE);

  FOR rec IN (
    SELECT employee_name
    FROM employees
    WHERE department_id = p_dept_id
    ORDER BY employee_name
  ) LOOP
    IF DBMS_LOB.GETLENGTH(v_result) > 0 THEN
      DBMS_LOB.WRITEAPPEND(v_result, 2, ', ');
    END IF;
    DBMS_LOB.WRITEAPPEND(v_result, LENGTH(rec.employee_name), rec.employee_name);
  END LOOP;

  RETURN v_result;
END;
/

-- Usage
SELECT department_id, build_employee_list(department_id) as emp_list
FROM departments;

Solution 5: JSON_ARRAYAGG (Oracle 12.2+)

-- Use JSON array instead of comma-separated string
SELECT department_id,
  JSON_ARRAYAGG(employee_name ORDER BY employee_name RETURNING CLOB) as emp_list
FROM employees
GROUP BY department_id;

Common Scenarios and Fixes

Dynamic SQL Generation

-- ERROR: Building long dynamic SQL
DECLARE
  v_sql VARCHAR2(4000);  -- Too small
BEGIN
  v_sql := 'SELECT ';
  FOR rec IN (SELECT column_name FROM user_tab_columns WHERE table_name = 'BIG_TABLE') LOOP
    v_sql := v_sql || rec.column_name || ', ';
  END LOOP;
  -- ORA-01489 if many columns
END;

-- FIX: Use CLOB for dynamic SQL
DECLARE
  v_sql CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_sql, TRUE);
  DBMS_LOB.WRITEAPPEND(v_sql, 7, 'SELECT ');
  FOR rec IN (SELECT column_name FROM user_tab_columns WHERE table_name = 'BIG_TABLE') LOOP
    DBMS_LOB.WRITEAPPEND(v_sql, LENGTH(rec.column_name) + 2, rec.column_name || ', ');
  END LOOP;
  -- DBMS_SQL supports CLOB for PARSE in 11g+
END;
/

Report Column Concatenation

-- ERROR: Concatenating address fields
SELECT customer_id,
  line1 || ', ' || line2 || ', ' || line3 || ', ' ||
  city || ', ' || state || ', ' || postal || ', ' || country as full_address
FROM addresses;

-- FIX: Cast first element to CLOB
SELECT customer_id,
  TO_CLOB(line1) || ', ' || line2 || ', ' || line3 || ', ' ||
  city || ', ' || state || ', ' || postal || ', ' || country as full_address
FROM addresses;

Prevention Strategies

1. Pre-Check String Lengths

-- Before running LISTAGG, verify it won't overflow
SELECT grouping_col,
  SUM(LENGTH(value_col)) + COUNT(*) - 1 as total_length
FROM your_table
GROUP BY grouping_col
HAVING SUM(LENGTH(value_col)) + COUNT(*) - 1 > 4000;

2. Default to CLOB for Large Concatenations

-- When in doubt, start with TO_CLOB to prevent the error
SELECT TO_CLOB(col1) || col2 || col3 FROM ...

3. Use ON OVERFLOW TRUNCATE as Standard Practice

-- Always add ON OVERFLOW clause to LISTAGG
LISTAGG(col, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY col)

Related Errors

• ORA-00910: Specified Length Too Long - Column definition exceeds datatype limit
• ORA-12899: Value Too Large for Column - Insert/update value exceeds column size
• ORA-06502: PL/SQL Numeric or Value Error - PL/SQL variable overflow
• ORA-00932: Inconsistent Datatypes - CLOB/VARCHAR2 type mismatch