Re: PL/SQL Exception Handling

Once your logic goes to your exception handler, there is no way back. I suggest that you recode your script to not handle your dups through an exception.

cheers,
bb

In article <t3ldtthft907af_at_corp.supernews.com>,   Anthony Valentine <amv_at_sbsslaska.com> wrote:
>
> 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
>
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 15 2000 - 22:05:27 CST