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: <3aa1212a.60152114@news-server>
References: <3aa0f50e.48860397@news-server> <3aa0f875.49731079@news-server> <3aa10ca8.23941265@news.online.de>
X-Newsreader: Forte Free Agent 1.21/32.243
Lines: 74
Date: Sat, 03 Mar 2001 16:51:37 GMT
NNTP-Posting-Host: 65.27.179.8
X-Complaints-To: abuse@rr.com
X-Trace: typhoon.kc.rr.com 983638297 65.27.179.8 (Sat, 03 Mar 2001 10:51:37 CST)
NNTP-Posting-Date: Sat, 03 Mar 2001 10:51:37 CST


Ted,

I know how to do that.

The question is can you find out without
inserting one line at a time via the cursor.

Thanks.


On Sat, 03 Mar 2001 15:35:30 GMT, knijff@bigfoot.com (Ted Knijff)
wrote:

>Well I don't know about the best way, but a small PL/SQL script
>something like :
>
>declare cursor cInput as
>  select foo, bar from table_source;
>begin
>  for cInputRec in cInput loop
>    insert into table_test (col1, col2)
>       values (cInputRec.foo, cInputRec.bar) ; 
>  end loop;
> exception   
>  dbms_output.put_line('Failed
>'||to_char(cInputRec.foo)||to_char(cInputRec.bar);
>end;
>
>Better suggestions anyone ?
>
>hth,
>
>Ted
>
>On Sat, 03 Mar 2001 13:59:00 GMT, gteets@rr.cinci.com (Greg Teets)
>wrote:
>
>>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.
>>
>
>EMail: knijff@bigfoot.com