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


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.