Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

[balajiyes]

Hi All

I am getting this error.

Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

Can some tell me a work around?

I am goin thru this doc http://asktom.oracle.com/pls/ask/f?p...:2367352052686

Many Thanks

[balajiyes]

This is my Query...

procedure pr_create_payment_invoice (
ip_batch_load_id in number,
ip_file_id in number
) is
c_unit_name varchar2(30):='pr_create_payment_invoice';

l_counter NUMBER := 0;
l_insert_count NUMBER := 0;

TYPE t_invoice_id IS TABLE OF pk_fin_data_source.t_invoice;
l_invoice_id t_invoice_id;

CURSOR cr_get_invoice_id(cp_batch_load_id in number) IS
SELECT /*+ use_hash(invoice) */
DISTINCT sfpi.invoice_id invoice_id
,invoice.customer_account_invoice_str invoice_no
,sfpi.payment_id
,NULL
BULK COLLECT INTO l_invoice_id
FROM stage_fin_payment sfp
,stage_fin_payment_invoice sfpi
,(SELECT /*+ no_merge */
invoice_id
,customer_account_invoice_str
FROM stage_fin_invoice sfi
WHERE batch_load_id = cp_batch_load_id
GROUP BY invoice_id
,customer_account_invoice_str) invoice
WHERE sfp.to_account_id = sfpi.account_id
AND sfp.payment_id = sfpi.payment_id
AND sfp.from_account_id IS NULL
AND sfp.batch_load_id = sfpi.batch_load_id
AND sfp.batch_load_id = cp_batch_load_id
AND EXISTS
(SELECT 1
FROM invoice_col ic
WHERE sfpi.invoice_id = ic.invoice_id)
AND sfpi.invoice_id = invoice.invoice_id(+);

begin

pk_globals.pr_add_call(GC_PACKAGE_NAME||'.'||c_unit_name);

OPEN cr_get_invoice_id(ip_batch_load_id);

LOOP
FETCH cr_get_invoice_id BULK COLLECT INTO l_invoice_id LIMIT GC_COMMIT_SIZE;

l_counter := l_invoice_id.COUNT;
l_insert_count := l_insert_count + l_counter;

--
FORALL l_invoiceid IN 1..l_counter
insert into tallyman_interface_line (
file_id,
line_type,
sequence_no,
batch_load_id,
interface_line_1,
interface_line_2,
record_created_timestamp
) select
ip_file_id,
2,
tallyman_line_no_seq.nextval,
ip_batch_load_id,
customer_account_invoice_str --CR 75539
||'|'||current_due
||'|'||case when (payment_date + grace_period_days > payment_extract_time)
then 0 else 1 end
||'|'||null
,
null,
sysdate
from stage_fin_invoice,
stage_control
where batch_load_id = ip_batch_load_id
and invoice_id = l_invoice_id.invoice_id(l_invoiceid);

COMMIT;

EXIT WHEN cr_get_invoice_id%NOTFOUND;

END LOOP;

CLOSE cr_get_invoice_id;

[LKBrwn_DBA]

Your error is with the table definition in:

TYPE t_invoice_id IS TABLE OF pk_fin_data_source.t_invoice;
l_invoice_id t_invoice_id;

You need to define separate tables for each column in the SELECT...BULK COLLECT INTO...
For example:

TYPE TXT_TYP IS TABLE OF VARCHAR2(100)
     INDEX BY BINARY_INTEGER;
V_INVOICE_ID TXT_TYP;

[balajiyes]

Thank. I got t.

[aravinds]

This is the error i am getting:-
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

And my Procedure is as follows:-
DECLARE
v_count NUMBER;
TYPE ARRAY IS TABLE OF sourcing%ROWTYPE;
l_data ARRAY;
p_array_size PLS_INTEGER DEFAULT 5;

CURSOR c IS SELECT * FROM sourcing where rownum<10 ;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
update sourcing set DISC = trunc(to_date('01/01/1901','MM/DD/YYYY'));
COMMIT;
EXIT WHEN c%NOTFOUND;
END LOOP;

CLOSE c;



Please help me in this regard

[davey23uk]

update sourcing set DISC = trunc(to_date('01/01/1901','MM/DD/YYYY')); is the only statement you need as you are changing every row in the table in every iteration of the loop.

and stop fetching across a commit - you will end up with ORA-0155

[gandolf989]

update sourcing set DISC = trunc(to_date('01/01/1901','MM/DD/YYYY')); is the only statement you need as you are changing every row in the table in every iteration of the loop.

and stop fetching across a commit - you will end up with ORA-0155

I disagree with both of your answers... There are restrictions on using a FORALL clause. The original poster should change his code to a regular for loop.

FOR i IN 1..l_data.COUNT
LOOP
...
END LOOP;
COMMIT;

You don't need to commit that much, you might be able to only commit at the end.