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

I must have been asleep, this should work :

declare cursor cInput as
  select foo, bar from table_source;
  lc char(20);
begin
  for cInputRec in cInput loop
   begin
    lc := to_char(cInputRec.foo);
    insert into table_test (col1, col2)

       values (cInputRec.foo, cInputRec.bar) ;    exception when others then
    dbms_output.put_line('Failed ' || lc);   end;
end;

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 - 11:31:25 CST