Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

insert %ROWTYPE + 1 column

bpolarskiApr 4 2011 — edited Apr 4 2011
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.
This post has been answered by Peter Gjelstrup on Apr 4 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 2 2011
Added on Apr 4 2011
4 comments
1,216 views