ORA-6502 From Execute Immediate When in a Loop and Statement is > 32K & < 64K (Doc ID 1367929.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

Concatenate an update statement > 32k and execute it using execute immediate.

Executes immediate the concatenated update statement 10 times in a loop.

In 11.2.0.1 and higher the code fails on the execute immediate with ORA-6502. The exact same code works prior to 11.2. Also adding dbms_output statement to the loop will resolve the error

create table dept_dummy( ORDER_ID NUMBER(28), ADV_ORDER_TIME_FIELD_012 DATE);

declare
a1 varchar(32767) := NULL;
begin
for i in 1..4091 loop
a1 := a1||' AND 1=1';
end loop;
for j in 1..10 loop
-- uncommenting this line will also resolve the 6502 error
-- dbms_output.put_line('Length of a1 is '||length(a1));
execute immediate 'update dept_dummy set order_id = order_id where 1=1'||a1||a1;
end loop;
end;
/

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms