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

Home -> Community -> Usenet -> c.d.o.tools -> Re: PL/SQL Exception Handling

Re: PL/SQL Exception Handling

From: Paul Scott <aspscott_at_tcp.co.uk>
Date: Sat, 16 Dec 2000 01:01:22 +0000
Message-ID: <i7fl3tcq6m7tr6k9ogadjsm7e5nmgvlm35@4ax.com>

You need to make sure the excetion block is within the cursor loop. Like this :

begin
  for recBranch in curBranch
  loop
    begin

      INSERT INTO 
        los160master 
      VALUES
       (recBranch.company_id,
        recBranch.sales_branch_id,
        recBranch.record_code,
            .
            .
            .
        recBranch.sub_qty_sold);      
    exception
      when DUP_VAL_ON_INDEX then
        INSERT INTO 
          los160master_dups 
        VALUES
          (recBranch.company_id,
           recBranch.sales_branch_id,
           recBranch.record_code,
            .
            .
            .
           recBranch.sub_qty_sold);        
    end;
  end loop;
  commit;
end;

Paul Scott
aspscott_at_tcp.co.uk
^^ remove 'as' anti-spam prefix
>
>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 - 19:01:22 CST

Original text of this message

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