Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Help Required in writing pl/sql block

Help Required in writing pl/sql block

From: Mike <learning_dba_at_yahoo.com>
Date: 11 Apr 2003 03:58:53 -0700
Message-ID: <d0966715.0304110040.7de6ff37@posting.google.com>


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; --}

END; --} Received on Fri Apr 11 2003 - 05:58:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US