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

From: Gerard Averill <gaverill_at_chsra.wisc.eduNOSPAM>
Date: 6 Mar 2001 20:14:00 GMT
Message-ID: <905C95890gaverill@144.92.88.10>

gteets_at_rr.cinci.com (Greg Teets) wrote in <3aa0f875.49731079_at_news-server>:

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

You could use a package to accomplish this:

create package logger
as

Overloaded log functions

function log(row_data varchar2, ret_value number) return number; pragma restrict_references(log, rnds, wnds, rnps);

function log(row_data varchar2, ret_value varchar2) return varchar2; pragma restrict_references(log, rnds, wnds, rnps);

function log(row_data varchar2, ret_value date) return date; pragma restrict_references(log, rnds, wnds, rnps);

Retrieve last logged row

function last_row return varchar2;
pragma restrict_references(last_row, rnds, wnds, wnps);

end;
/

create package body logger
as

Overloaded log functions

  function log(row_data varchar2, ret_value number) return number   as
  begin
    last_row_data := row_data;
    return ret_value;
  end;

  function log(row_data varchar2, ret_value varchar2) return varchar2   as
  begin
    last_row_data := row_data;
    return ret_value;
  end;

  function log(row_data varchar2, ret_value date) return date   as
  begin
    last_row_data := row_data;
    return ret_value;
  end;

Retrieve last logged row

  function last_row return varchar2
  as
  begin
    return last_row_data;
  end;

end;
/

create table table_test
(
col1 number(10) constraint uq_table_test unique , col2 varchar2(10)
)
/

create table table_source
(
foo number(10)
, bar varchar2(50)
)
/

insert
into table_source

select 1, 'one' from dual union all
select 2, 'two' from dual union all
select 3, 'three' from dual union all
select 3, 'three again' from dual

begin
  insert
  into table_test
  select logger.log(to_char(rowid), foo), bar   from table_source;
exception
when others then
  dbms_output.put_line('insert failed on row ' || logger.last_row);   raise;
end;
/

The package uses a private variable to save the row data sent to any of the overloaded LOG functions. On an exception, the last saved row data can be obtained using the LAST_ROW function.

Note that I have not tested this thoroughly; I'll leave that to you if you think this approach may be useful.

-- 
Gerard Averill
gaverill<at>chsra<dot>wisc<dot>edu

Received on Tue Mar 06 2001 - 14:14:00 CST