Re: PLSQL exception handling
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: peter.schauss@xxxxxxx
- Date: Thu, 9 Jun 2011 23:09:15 +0300
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@xxxxxxx>:
> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
