Re: PLSQL exception handling
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: peter.schauss@xxxxxxx
- Date: Thu, 09 Jun 2011 22:01:04 +0200
Peter,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
