Getting ORA-04030 Error While Executing A Procedure Having BULK COLLECT (Doc ID 1221283.1)

Last updated on NOVEMBER 13, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Symptoms


Getting ORA-04030 while executing a procedure that deals with high number of historical records.
Basically this procedure used for archiving the old records. It functions by selecting the records first,then putting in a file that resides in the server, then the corresponding records will be deleted. ORA-4030 error during the SELECT phase.

The procedure uses BULK COLLECT clause as below:

FOR i IN 1 .. v_ndays LOOP
OPEN logs_1;
FETCH logs_1 BULK COLLECT
INTO v_err_msg;
CLOSE logs_1;
-- Archive oldest data into log files and delete in the main tables
FOR indx IN 1 .. v_err_msg.COUNT LOOP
IF v_flipcontrol = 0 THEN
update_emc;
dbfl_error.raise_error('I'
,'Job Manually exited '
,pk_name ||
proc_name);
RETURN;
END IF;
archive_log(v_err_msg(indx)
.TIMESTAMP
, v_err_msg(indx)
.SOURCE
, v_err_msg(indx)
.message
, v_err_msg(indx)
.severity);
END LOOP;

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