PLSQL - handling errors when using an implicit cursor
- From: "Schauss, Peter (ESS)" <peter.schauss@xxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Jun 2011 20:20:41 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
