Re: PLSQL exception handling

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 9 Jun 2011 23:09:15 +0300
Message-ID: <BANLkTikzYPmycDvq0Dx8uokqFwfmtK8vSQ_at_mail.gmail.com>

It seems you are doing some kind of data migration. I suggest you to try simple SQL approach if it is possible. Instead of pseudocode: For each record in source table:
  Copy it to pl/sql;
  Make transformation on column1;
  ...
  Make transformation on column n;
  Insert into target table
End for;

Better create some temporary table (or several temporary tables, might be global temporary tables or not, doesn't matter so much) with necessary structure and do something like: Copy all records from source into temporary table doing as much transformations already as possible;
Do transformation 1 on all records;
...
Do transformation n on all records;
Insert all records into target table.

If you need to fix some kind of logical error for each record why it fails transformations, then I suggest to use concept of validations - before making transformation mark all invalid records with your error number BEFORE transformation.
Even if you'll scan all the data several times (for validations, transformations etc), it still will be better than doing row by row approach.
I have done a few such migrations and even wrote an article about them http://www.gplivna.eu/papers/legacy_app_migration.htm

Of course each case is different, but use records and cursors (even bulks and foralls) only as a last resort.

Gints Plivna
http://www.gplivna.eu

2011/6/9 Schauss, Peter (ESS) <peter.schauss_at_ngc.com>:
> Oracle 11.2.0.1.0 (Linux x86-64).
>
> I am writing stored procedure which copies information from one table to another, doing some transformations in the process.  I am using the %rowtype construct for the fetches and inserts and doing a large number of assignment statements in the form:
>
> rec1.col1 := rec2.cola;
>
> Since there is a possibility of type conversion errors in some cases, I need to be able to trap errors and identify the offending column in the input table.  The Oracle documentation suggests something like this:
>
> step_num:= <n>
> rec1.col1 := rec2.cola;
> step_num:=<n+1>
> rec1.col2 :=rec2.colb;
>
> exception
>        when <error type> dbms_output.put_line('error at '||step_num);
>        raise;
> end;
>
> Is there a better way for me to identify the location of the error?
>
> Thanks,
> Peter Schauss
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 09 2011 - 15:09:15 CDT