-
Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
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
Cheers!
OraKid.
-
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;
Cheers!
OraKid.
-
Your error is with the table definition in:
Code:
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:
Code:
TYPE TXT_TYP IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;
V_INVOICE_ID TXT_TYP;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
-
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
-
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
-
Originally Posted by 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
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.
Code:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|