Why didn't you go with the error logging solution? For example .. INSERT INTO dest SELECT * FROM source LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED; Mike On Fri, Jul 1, 2011 at 1:25 PM, Schauss, Peter (ESS) <peter.schauss@xxxxxxx>wrote: > Kevin, > > 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@xxxxxxxxxx] > Sent: Wednesday, June 29, 2011 5:13 PM > To: Schauss, Peter (ESS); oracle-l@xxxxxxxxxxxxx > 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@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Schauss, Peter (ESS) > Sent: Wednesday, June 29, 2011 3:21 PM > To: oracle-l@xxxxxxxxxxxxx > 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 > > -- > //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. > > -- > //www.freelists.org/webpage/oracle-l > > >