| PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL [message #617597] |
Wed, 02 July 2014 03:24  |
 |
ho2512
Messages: 7
Registered: August 2013
|
Junior Member |
|
|
Create or Replace PROCEDURE BUILD_BALANCE_DATA
AS
TYPE Bal_Days_Type IS TABLE OF Avg_Balance_Det%ROWTYPE; -- Avg_Balance_Det is a DB table, with 15 columns
bal_days_tab Bal_Days_Type := Bal_Days_Type ();
v_bal_cntr NUMBER := 0;
BEGIN
For i in ( Select acc_no, ldgr_bal, avlbl_bal from daily_bal_det )
loop
/*
Some Calculation
some queries to fetch details
*/
bal_days_tab.EXTEND;
v_bal_cntr := v_bal_cntr + 1;
bal_days_tab(v_bal_cntr).acc_no := i.acc_no;
bal_days_tab(v_bal_cntr).ldgr_bal := i.ldgr_bal;
/*
Populating other columns of record type from calculated values and other table queries
*/
IF v_bal_cntr = 3000
THEN
FORALL i IN 1 .. v_bal_cntr
--INDICES OF bal_days_tab
INSERT INTO Avg_Balance_Det
( acc_no, ldgr_bal
/*
other table columns
*/
)
VALUES ( bal_days_tab(v_bal_cntr).acc_no, bal_days_tab(v_bal_cntr).ldgr_bal
/*
other record type columns
*/
);
COMMIT;
bal_days_tab.DELETE;
v_bal_cntr := 0;
END IF;
END LOOP;
END BUILD_BALANCE_DATA;
/
Please check the above procedure.
When i am compiling the above procedure it is giving an error - PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL .
This procedure will be inserting around 60000 to 90000 records and i don't want it to perform those many context swithces.
So inserting records in chunks of 3000 (or may be more).
I tries using FORALL in below two ways
FORALL i IN 1 .. v_bal_cntr
and
FORALL i IN INDICES OF bal_days_tab
but both are giving the same error.
Could you please help me to resolve the error or guide me to another way of achieving this.
Many Thanks in advance.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
