Re: PLSQL exception handling

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Thu, 9 Jun 2011 13:36:47 -0700
Message-ID: <BANLkTimTOViyH_KTtFezHXbe6THTsLkeZQ_at_mail.gmail.com>

*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_at_gmail.com> 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_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
>
>
>

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

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