Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Determining Which Row Failed In Insert ... Select Without Using A Cursor
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 - 09:35:30 CST