Re: PLSQL exception handling
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Jun 2011 13:36:47 -0700
*Peter,*
*When you said:*
" 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;"
*Did you actually mean:*
rec1 := rec2;
I get the impression that you are doing record level assignments and what
you are tying to avoid is doing the assignment on a column by column basis
in order to determine which column's data has a problem. Am I right?
Mike
On Thu, Jun 9, 2011 at 1:09 PM, Gints Plivna <gints.plivna@xxxxxxxxx> wrote:
> 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:
