Re: Determining Which Row Failed In Insert ... Select Without Using A Cursor

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_at_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_at_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_at_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_at_bigfoot.com
  Received on Sat Mar 03 2001 - 10:51:37 CST