Re: PLSQL exception handling

   What about :

       insert into new_table
       select duly_transformed_data
       from old_table
       log errors into errlog('migr')
       reject limit ... ?

There are very few transformations that a good use of case ... end and a couple of functions cannot take care of, this would allow you to process massively without having to commit every line for fear of losing all the work done at the first error, then to fix all the errors at one and load again. Just what you would do with SQL*Loader, actually.

My EUR 0.02.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


On 06/09/2011 09:12 PM, Schauss, Peter (ESS) wrote:


> 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