RE: PLSQL - handling errors when using an implicit cursor

  • From: "Schauss, Peter (ESS)" <peter.schauss@xxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 1 Jul 2011 20:25:20 +0000

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


Other related posts: