RE: PLSQL - handling errors when using an implicit cursor

From: Lange, Kevin G <kevin.lange_at_ppoone.com>
Date: Wed, 29 Jun 2011 16:13:18 -0500
Message-ID: <F077F09A0E11504D9E720358BEE994D1081520A8_at_APSW0553EVS.ms.ds.uhc.com>

None of the ways I can think of stay in your simple insert statement format.

Any way you can use an anonymous block that uses a cursor from the source table ? Inside the cursor loop take down information like the row id of the source record. Then, when the insert causes an error, use an exception block to display the saved rowid value.

If you want all records that are good to be inserted, then commit after each successful insert. Otherwise, rollback on the first failure.  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, Peter (ESS) Sent: Wednesday, June 29, 2011 3:21 PM
To: oracle-l_at_freelists.org
Subject: PLSQL - handling errors when using an implicit cursor

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

This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 29 2011 - 16:13:18 CDT