From: gteets@rr.cinci.com (Greg Teets)
Newsgroups: comp.databases.oracle.server
Subject: Re: Determining Which Row Failed In Insert  ... Select Without Using A Cursor
Message-ID: <3aa0f875.49731079@news-server>
References: <3aa0f50e.48860397@news-server>
X-Newsreader: Forte Free Agent 1.21/32.243
Lines: 34
Date: Sat, 03 Mar 2001 13:59:00 GMT
NNTP-Posting-Host: 65.27.179.8
X-Complaints-To: abuse@rr.com
X-Trace: typhoon.kc.rr.com 983627940 65.27.179.8 (Sat, 03 Mar 2001 07:59:00 CST)
NNTP-Posting-Date: Sat, 03 Mar 2001 07:59:00 CST


I wasn't awake yet when I typed the first one.

Here's the correction::

INSERT INTO table_test(col1, col2)
SELECT foo, bar
FROM table_source;

Thanks.



On Sat, 03 Mar 2001 13:52:14 GMT, gteets@rr.cinci.com (Greg Teets)
wrote:

>Is there a direct way (without using a cursor) to determine which row
>failed when there is an error in the following example bulk insert
>statement.  We are using Oracle 7.3.
>
>INSERT INTO table_test(col1, col2)
>SELECT(foo, bar);
>
>I am optimizing this package for speed.  This statement will need to
>process hundreds of thousands of rows.  
>
>Because the data has been scrubbed for forseeable errors we expect
>few inserts to fail.  However, if one does fail the user would like to
>see the row in an error file.
>
>If I can't find the row directly without using a cursor, I have some
>code that I can put in the exception section or I can use SQL loader.
>
>Thanks in advance.