This discussion is archived
0 Replies Latest reply: May 3, 2013 7:38 AM by 961076 RSS

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

961076 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points