DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01406 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01406: Fetched Column Value Was Truncated — Cause & Fix

ORA-01406 occurs when Oracle tries to fetch a column value into a host variable or bind variable whose buffer size is smaller than the actual data being retrieved. This error is most common in Pro*C, OCI, JDBC, and ODBC applications where the receiving variable is not large enough to hold the full column value. Unlike a runtime SQL error, this is typically a programming or configuration issue that requires a code-level fix.

Top 3 Causes

1. Host Variable Buffer Too Small (Pro*C / OCI)

The most frequent cause. The variable declared in the client application is shorter than the actual data stored in the database column.

-- First, check the actual column definition and real data length
SELECT column_name,
       data_type,
       data_length
FROM   user_tab_columns
WHERE  table_name  = 'EMPLOYEES'
AND    column_name = 'JOB_TITLE';

-- Check the maximum actual data length stored
SELECT MAX(LENGTH(job_title)) AS max_actual_length
FROM   employees;
Enter fullscreen mode Exit fullscreen mode

If max_actual_length exceeds your declared buffer size in C/Java, ORA-01406 will be thrown.

2. PL/SQL Variable Declared with Insufficient Size

Hardcoding a fixed size for a PL/SQL variable instead of referencing the column type causes the error when data grows beyond the declared length.

-- BAD: Hardcoded size that may be too small
DECLARE
    v_notes VARCHAR2(100); -- Column is VARCHAR2(2000)
BEGIN
    SELECT notes
    INTO   v_notes
    FROM   orders
    WHERE  order_id = 5001; -- Fails if notes > 100 chars
END;
/

-- GOOD: Use %TYPE to automatically match column definition
DECLARE
    v_notes orders.notes%TYPE; -- Always matches the column size
BEGIN
    SELECT notes
    INTO   v_notes
    FROM   orders
    WHERE  order_id = 5001;

    DBMS_OUTPUT.PUT_LINE('Notes: ' || v_notes);
EXCEPTION
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Buffer overflow detected.');
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Fetching LONG or Large String Columns Without Proper Handling

Columns defined as LONG or very wide VARCHAR2 columns are high-risk when mapped to fixed-size buffers. Using SUBSTR or converting to CLOB is the recommended approach.

-- Convert LONG column to CLOB to handle safely
CREATE TABLE new_orders AS
SELECT order_id,
       TO_LOB(long_notes) AS notes_clob
FROM   old_orders;

-- Use SUBSTR to safely limit fetch size when full data is not needed
SELECT order_id,
       SUBSTR(notes, 1, 500) AS notes_preview
FROM   orders
WHERE  order_id = 5001;

-- For CLOB columns, use DBMS_LOB.SUBSTR
SELECT order_id,
       DBMS_LOB.SUBSTR(notes_clob, 500, 1) AS notes_preview
FROM   new_orders
WHERE  order_id = 5001;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 — Always use %TYPE or %ROWTYPE in PL/SQL:

DECLARE
    r_order orders%ROWTYPE; -- Entire row, all types auto-matched
BEGIN
    SELECT * INTO r_order
    FROM   orders
    WHERE  order_id = 5001;
    DBMS_OUTPUT.PUT_LINE(r_order.notes);
END;
/
Enter fullscreen mode Exit fullscreen mode

Fix 2 — Validate data lengths before deployment:

-- Run this before deploying any new feature touching string columns
SELECT column_name,
       data_length        AS defined_length,
       MAX(LENGTH(notes)) AS actual_max_length,
       CASE
           WHEN MAX(LENGTH(notes)) > data_length THEN 'WARNING: Data exceeds column length'
           ELSE 'OK'
       END AS check_status
FROM   user_tab_columns,
       orders
WHERE  table_name  = 'ORDERS'
AND    column_name = 'NOTES'
GROUP BY column_name, data_length;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Enforce %TYPE usage as a coding standard. Never hardcode variable sizes in PL/SQL. Using %TYPE ensures your code automatically adapts when a DBA resizes a column, eliminating ORA-01406 at the source.

  2. Add a pre-deployment data length validation step to your CI/CD pipeline. Run a query that compares MAX(LENGTH(column)) against DATA_LENGTH from USER_TAB_COLUMNS for all columns touched by a release. This catches mismatches before they hit production.

Related Errors

  • ORA-06502 — PL/SQL: numeric or value error: character string buffer too small: Closely related; often appears alongside ORA-01406 in PL/SQL contexts.
  • ORA-01401 — inserted value too large for column: The write-side equivalent of ORA-01406.
  • ORA-01403 — no data found: Another common FETCH-related error to handle together in exception blocks.

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