Re: PLSQL - handling errors when using an implicit cursor

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: peter.schauss@xxxxxxx
  • Date: Fri, 1 Jul 2011 13:30:11 -0700

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
>
>
>

Other related posts: