Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Help Required in writing pl/sql block
Hi,
I have a requirement.I have some values in table xx_payments.Taking
those values from xx_paymentsI need to run a procedure bmf_insert.
This procedure bmf_insert updates a table called bmf which also has a
field tracking_id.I'll also have to update the same tracking_id in
xx_payments table as
well so that if the tracking_id exists in xx_payments already then
running the procedure again should result in a error.
Can someone help me write the pl/sql block with the above
requirements.
I have attached the code written.Can someone modify it for me?
TIA
Mike
DECLARE
l_max_no NUMBER;
BEGIN --{
BEGIN --{ SELECT MAX(seq_num) INTO l_max_no FROM seq_num WHERE table_name='BMF_TABLE' ; EXCEPTION WHEN NO_DATA_FOUND THEN l_max_no := 0; WHEN OTHERS THEN l_max_no := 0; END; --} FOR cx IN (SELECT col1, col2 , col3 FROM xx_payments WHERE condition ) LOOP --{ BEGIN --{ execute bmf_insert(values,l_max_no) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN do_reqd_taks WHEN OTHERS THEN do_reqd_taks; END; --} END LOOP; --}