Oracle ORA-06505 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06505: PL/SQL Variable Requires More Than 32767 Bytes of Contiguous Memory

ORA-06505 is thrown by Oracle's PL/SQL engine when a variable demands more than 32,767 bytes (approximately 32KB) of contiguous memory in a single allocation. This hard limit applies to scalar PL/SQL types such as VARCHAR2 and RAW. The error is most commonly triggered during large string manipulations, dynamic SQL construction, or when reading oversized data from external sources into a PL/SQL variable.

Top 3 Causes

1. VARCHAR2 Variable Exceeds the 32,767-Byte Limit

The PL/SQL VARCHAR2 type has a strict ceiling of 32,767 bytes. Repeated string concatenation in loops is the most common culprit.

-- Problematic code: concatenation blows past 32,767 bytes
DECLARE
    v_text VARCHAR2(32767);
BEGIN
    FOR i IN 1..10000 LOOP
        v_text := v_text || 'Record_' || i || ', '; -- ORA-06505 eventually
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_text);
END;
/

-- Fix: switch to CLOB
DECLARE
    v_text CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_text, TRUE);
    FOR i IN 1..10000 LOOP
        DBMS_LOB.APPEND(v_text, 'Record_' || i || ', ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Length: ' || DBMS_LOB.GETLENGTH(v_text));
    DBMS_LOB.FREETEMPORARY(v_text);
END;
/

2. Dynamic SQL String Construction Overflow

Building large dynamic SQL statements — especially with thousands of IN-list values — quickly exhausts the VARCHAR2 limit.

-- Problematic: dynamic SQL stored in VARCHAR2
DECLARE
    v_sql VARCHAR2(32767);
BEGIN
    v_sql := 'SELECT * FROM orders WHERE id IN (';
    FOR i IN 1..4000 LOOP
        v_sql := v_sql || i || ',';  -- ORA-06505 around iteration 3000+
    END LOOP;
    EXECUTE IMMEDIATE RTRIM(v_sql, ',') || ')';
END;
/

-- Fix: use CLOB with DBMS_SQL
DECLARE
    v_sql    CLOB;
    v_cursor INTEGER;
    v_rows   INTEGER;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_sql, TRUE);
    DBMS_LOB.APPEND(v_sql, 'SELECT * FROM orders WHERE id IN (');
    FOR i IN 1..4000 LOOP
        IF i > 1 THEN DBMS_LOB.APPEND(v_sql, ','); END IF;
        DBMS_LOB.APPEND(v_sql, TO_CLOB(i));
    END LOOP;
    DBMS_LOB.APPEND(v_sql, ')');

    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    v_rows := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    DBMS_LOB.FREETEMPORARY(v_sql);
END;
/

3. RAW Variable Used for Binary Data

The RAW PL/SQL type shares the same 32,767-byte ceiling. Attempting to load image or document data into a RAW variable will trigger ORA-06505.

-- Problematic: reading large binary into RAW
DECLARE
    v_data RAW(32767);
BEGIN
    SELECT binary_col INTO v_data FROM file_store WHERE id = 1; -- ORA-06505
END;
/

-- Fix: use BLOB with chunk-based reading
DECLARE
    v_blob   BLOB;
    v_chunk  RAW(2000);
    v_offset INTEGER := 1;
    v_amount INTEGER := 2000;
    v_total  INTEGER;
BEGIN
    SELECT binary_col INTO v_blob FROM file_store WHERE id = 1;
    v_total := DBMS_LOB.GETLENGTH(v_blob);

    WHILE v_offset <= v_total LOOP
        DBMS_LOB.READ(v_blob, v_amount, v_offset, v_chunk);
        -- Process each chunk here
        v_offset := v_offset + v_amount;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Done. Total bytes: ' || v_total);
END;
/

Quick Fix Summary

Situation	Replace With
Large VARCHAR2 variable	`CLOB`
Large RAW variable	`BLOB`
Dynamic SQL in VARCHAR2	`CLOB` + `DBMS_SQL`
Chunked processing needed	`DBMS_LOB.READ` / `DBMS_LOB.SUBSTR`

Prevention Tips

Use CLOB/BLOB by default for potentially large data. Establish a coding standard that mandates CLOB for any variable that could hold more than 10,000 characters. Catching this during code review is far cheaper than debugging it in production.

Include boundary-value test cases. Always test PL/SQL procedures with the maximum realistic data size, not just average-case data. Dynamic SQL routines should be tested with the largest possible IN-list or string payload to surface ORA-06505 before it reaches your users.

Related Errors

ORA-06502 – Value error / character string buffer too small
ORA-01489 – Result of string concatenation is too long (SQL layer)
ORA-22813 – Operand value exceeds system limits (LOB operations)

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

DEV Community