RE: PLSQL - handling errors when using an implicit cursor

Thanks for the suggestion. Here is what I ended up doing:

• use a cursor and a fetch bulk collect to get the rowids from the source table.
• create an insert statement in the form insert into <table> (...) values (...) where rowdid = :j in a loop
• execute immediate <insert statement> using <rowid>

Now if one of the inserts fails, I have the rowid of the offending row in the source table.

I assume that my use of a bind variable will minimize the recompile overhead.

Hope this will be useful to others.

• Peter Schauss

-----Original Message-----
From: Lange, Kevin G [mailto:kevin.lange_at_ppoone.com] Sent: Wednesday, June 29, 2011 5:13 PM
To: Schauss, Peter (ESS); oracle-l_at_freelists.org Subject: EXT :RE: PLSQL - handling errors when using an implicit cursor

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