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
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
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);
function last_row return varchar2;
pragma restrict_references(last_row, rnds, wnds, wnps);
end;
/
create package body logger
as
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;
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.
g
-- Gerard Averill gaverill<at>chsra<dot>wisc<dot>eduReceived on Tue Mar 06 2001 - 14:14:00 CST
![]() |
![]() |