4 Replies Latest reply: Apr 4, 2011 4:29 AM by Peter Gjelstrup RSS

    insert %ROWTYPE + 1 column

      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 )
                  insert into $towner.$ttable values rec ;
                 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.
        • 1. Re: insert %ROWTYPE + 1 column
          You'll have to specify all the columns and values... e.g.
          insert into $ERR_OWNER.$ERR_TBL  values (rec.mykeycol, rec.datacol1, rec.datacol2, rec.datacol3 etc. etc.... ,'reason ...') ;
          You can't mix a 'wildcard' with a fixed value in the way you were trying.

          If you're trying to make something generic so it handles different tables then you're going to have to use dynamic SQL statements and obtain the column names from the data dictionary (user_tab_cols etc.) and build up your insert statement as a string before executing it with either EXECUTE IMMEDIATE or the DBMS_SQL package.
          • 2. Re: insert %ROWTYPE + 1 column
            Peter Gjelstrup

            Looks like you are re-inventing DML Error Logging?


            Unless of course your version - which you forgot to mention - does not support this.

            • 3. Re: insert %ROWTYPE + 1 column
              I was simply not aware of this package but this is exactly what I want.

              • 4. Re: insert %ROWTYPE + 1 column
                Peter Gjelstrup
                It is the 10g killer feature ;)

                Have in mind that you should not use a cursor, do it i straight SQL whenever you can.

                Something like:
                insert into $towner.$ttable
                  select * from $owner.$table
                log errors into ....