Re: Help Required in writing pl/sql block

Originally posted by Mike
> 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.
>

Based on that requirements definition and no information about your tables - no!

>
> I have attached the code written.Can someone modify it for me?
>
> 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; --}
> END; --}

I would use a SEQUENCE, not a table called seq_num. And the NO_DATA_FOUND and DUP_VAL_ON_INDEX exception handlers are redundant since they do the same as the following WHEN OTHERS handlers.

--
Posted via http://dbforums.com