0 Replies Latest reply: May 3, 2013 9:38 AM by 961076 RSS

    unable to get old values( to compare old and new values)

    961076
      I am trying to compare the old and new values in the lcr and based on the result i want to replicate.

      CREATE OR REPLACE PROCEDURE check_dml (in_any in sys.anydata)
      IS
      lcr           SYS.LCR$_ROW_RECORD;
      rc            PLS_INTEGER;
      new_id number;
      new_id_anydata ANYDATA;
      old_id number;
      old_id_anydata ANYDATA;
      BEGIN
      rc := in_any.GETOBJECT(lcr);

      old_id_anydata := lcr.GET_VALUE('old','id');
      rc := old_id_anydata.GETVARCHAR2(old_id);
      new_id_anydata := lcr.GET_VALUE('new','id');
      rc := new_id_anydata.GETVARCHAR2(new_id);


      if old_id>=new_id
      then lcr.set_values('id', NULL);
      end if;

      lcr.execute(true);
      END;
      +/+


      BEGIN
      DBMS_APPLY_ADM.SET_DML_HANDLER
      +(object_name => 'SCOTT.TAJ',+
      object_type        => 'TABLE',
      operation_name     => 'UPDATE',
      error_handler      => FALSE,
      user_procedure     => 'STRMADMIN.CHECK_DML',
      apply_database_link=> NULL);
      END;
      +/+




      *(the column id is primary key so if i set its values null it wont be copied to target table)*

      but the problem here is i am not getting old values ... i tested it by creating "status" table in strmadmin(and also in scott) and entering the old, new values in that table.

      CREATE OR REPLACE PROCEDURE dml_check (in_any IN SYS.ANYDATA)
      IS

      lcr           SYS.LCR$_ROW_RECORD;
      rc            PLS_INTEGER;
      new_data number;
      new_data_anydata ANYDATA;
      old_data number;
      old_data_anydata ANYDATA;

      BEGIN
      -- Access the LCR
      rc := in_any.GETOBJECT(lcr);


      old_data_anydata := lcr.GET_VALUE('old','data');
      rc := old_data_anydata.GETVARCHAR2(old_data);
      new_data_anydata := lcr.GET_VALUE('new','data');
      rc := new_data_anydata.GETVARCHAR2(new_data);


      insert into status values(new_data,old_data);


      END;
      +/+

      BEGIN
      DBMS_APPLY_ADM.SET_DML_HANDLER
      +(object_name => 'SCOTT.TAJ',+
      object_type        => 'TABLE',
      operation_name     => 'UPDATE',
      error_handler      => FALSE,
      user_procedure     => 'STRMADMIN.DML_CHECK',
      apply_database_link=> NULL);
      END;
      +/+


      but the table is always empty(insert , update , delete  are being replicated)

      Edited by: 958073 on May 3, 2013 8:07 PM