Re: PL/SQL Exception Handling

From: Frederick C. Lee <fclee_at_earthlink.net>
Date: Fri, 29 Dec 2000 04:32:29 GMT
Message-ID: <B6715412.252C%fclee@earthlink.net>

You can always place your block into a larger block within a loop. Thusly:

begin
  open cursor...
  loop

     fetch cursor...
     exit when cursor%notfound;
     begin
        <code>
     exception
         when dupl
         ...
         ... 
         etc.
     end block;

   end loop;
end block;

That way you remain in *control* within your loop.

Ric.

>>
>> 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 Thu Dec 28 2000 - 22:32:29 CST