My Oracle Support Banner

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

Last updated on NOVEMBER 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or built-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.


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;

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.