From: knijff@bigfoot.com (Ted Knijff)
Newsgroups: comp.databases.oracle.server
Subject: Re: Determining Which Row Failed In Insert  ... Select Without Using A Cursor
Date: Sat, 03 Mar 2001 17:31:25 GMT
Organization: 1&1 Internet AG
Lines: 55
Message-ID: <3aa1294d.31274490@news.online.de>
References: <3aa0f50e.48860397@news-server> <3aa0f875.49731079@news-server>
NNTP-Posting-Host: pd4b892f1.dip0.t-ipconnect.de
X-Trace: news.online.de 983640611 19104 212.184.146.241 (3 Mar 2001 17:30:11 GMT)
X-Complaints-To: abuse@online.de
NNTP-Posting-Date: 3 Mar 2001 17:30:11 GMT
X-Newsreader: Forte Free Agent 1.21/32.243


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@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