Re: PLSQL - handling errors when using an implicit cursor

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_at_ngc.com>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_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
>
>
>

--
http://www.freelists.org/webpage/oracle-l