Oracle Streams Add New Column in Target Table

Oracle Streams Add New Column in target Table



CREATE OR REPLACE PROCEDURE test_im_amc(evt IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
-- Access the LCR. RC holds the return code.
rc := evt.GETOBJECT(lcr);
--add a new column called commit_scn
lcr.ADD_COLUMN('new','COMMIT_SCN',SYS.AnyData.ConvertNumber(lcr.GET_SCN()));
-- Apply row LCR
lcr.EXECUTE(true);
END;


BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/