PL/SQL Exception Handling

Hello!!

I am new to PL/SQL (I don't even own any books on it yet) and am in need of a quick fix. I have to modify an existing PL/SQL procedure to copy rows from one table to another. I have added a unique constrint to the destination table and need to have the procedure write any duplicate rows to a different table.

Currently the procedure will run correctly until it finds the first duplicate, which it will write to the duplicate table then exit. I don't want it to exit, but to continue until it has read all rows from the souce table. I am guessing that it is a simple matter of knowing what command to give to tell it to return back to the starting loop.

I have tried using 'ROLLBACK to start_transaction' after the commit, but this gives me a runtime error that says that I need to declare start_transaction. I have tried using a label and goto but apparently I can't leave an exception with a goto.

Here is some system info and parts of the procedure in question. Please let me know if you need any further info.

System: IBM RS/6000 H80
OS: AIX 4.3.3
Oracle: 8.1.7

Procedure extracts:

BEGIN   OPEN branch_cursor;

  LOOP     SAVEPOINT start_transaction;

    FETCH branch_cursor INTO

       v_company_id,
       v_sales_branch_id,
       v_record_code,


            .

            .

            .

       v_sub_qty_sold;

EXIT WHEN branch_cursor%NOTFOUND;

INSERT INTO los160master VALUES

      (v_company_id,
       v_sales_branch_id,
       v_record_code,


            .

            .

            .

       v_sub_qty_sold);

COMMIT; CLOSE branch_cursor;

END LOOP; EXCEPTION

     WHEN DUP_VAL_ON_INDEX THEN
        INSERT INTO los160master_dups VALUES
           (v_company_id,
           v_sales_branch_id,
           v_record_code,


            .

            .

            .

           v_sub_qty_sold);

          COMMIT;

     WHEN NO_DATA_FOUND THEN
          ROLLBACK TO start_transaction;

END; Thanks in advance for any help!!

Anthony Valentine
amv_at_sbsalaska.com Received on Fri Dec 15 2000 - 18:30:53 CST