PLSQL exception handling

From: Schauss, Peter (ESS) <"Schauss,>
Date: Thu, 9 Jun 2011 19:12:35 +0000
Message-ID: <8AE45871F749FC4CBBE053CF2F8A493C0A6D94E6_at_XMBVAG74.northgrum.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

Received on Thu Jun 09 2011 - 14:12:35 CDT