PLSQL - handling errors when using an implicit cursor

From: Schauss, Peter (ESS) <"Schauss,>
Date: Wed, 29 Jun 2011 20:20:41 +0000
Message-ID: <8AE45871F749FC4CBBE053CF2F8A493C0A6F697B_at_XMBVAG74.northgrum.com>

I have a stored procedure which is copying and, in some cases, transforming data from one table to another. For some reasons related to the application requirements, I have to use code in the form:

insert into target_table (c1,c2, c3,...)

	(select s1,s2,s3,...) 
	from source_table
	where ...);

I am trying to work out a way to handle errors so that we can determine which row in the source table caused the insert to fail. The only thing I have thought of so far would be to use an ORDER BY clause in the select statement. Then the SQL%ROWCOUNT+1 would be the offending row in the source table. I could then retrieve that row with a select statement. Read consistency should insure that it would be the same row as long as I do not commit or roll back the transaction. Can anyone suggest a less cumbersome solution?

Thanks,
Peter Schauss

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

Received on Wed Jun 29 2011 - 15:20:41 CDT