I would like to insert rows into a target table from a source table with same structure.
When the insert fails du to a constraints, I want to insert the row to an errors table
and add the SQLERR code to the table.
Problem is that I don't know how to insert the modified row:
-- creating the err_table from source table and adding a column.
sqlcmd:='create table $ERR_OWNER.$ERR_TBL as select * from $owner.$table where 1=2' ;
dbms_output.put_line('Doing :' || sqlcmd ) ;
execute immediate sqlcmd ;
sqlcmd:='alter table $ERR_OWNER.$ERR_TBL add ( resync_err_reason varchar2(4000) default ''unknown'' )' ;
dbms_output.put_line('Doing :' || sqlcmd ) ;
.
.
-- copy the rows now from source to target table. Errors rows goes to Error_table
rec ${ttable}%ROWTYPE ;
for rec in (select * from $owner.$table )
loop
begin
insert into $towner.$ttable values rec ;
exception
when others then
insert into $ERR_OWNER.$ERR_TBL values (rec.*,'reason ...') ; <-- does not work
end ;
end loop;
as work around, I thought to create a structure like that :
type err_rec is record
( v_rec rec,
err_msg varchar2(4000)
) ;
v_err err_rec ;
and write the simple :
insert into $bk_owner.$err_table values v_err
But then I don't know how to assing the into the 'err_rec'.
I have still this missing column 'resync_err_reason' which is in the record and not in the %ROWTYPE.
Any other solution to bring into the ERR_TABLE the content of the original row + the SQLERR into the last column is most welcome.