Oracle Streams Delete Column

Oracle Streams Delete Column.

Source Schame Name : SCOTT

Source Table Name : EMP

Target Schame Name : SCOTT

Target Table Name : EMP

Delete Column Names : SAL,JOB

Two Steps.

1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.


1.Rule-based transformation.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='EMP';


begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'SAL',
operation => 'ADD');
END;
/

begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'JOB',
operation => 'ADD');
END;
/


2.Rename columns in the LCR using a DML Apply Handler.

rem Create DML handler procedure

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

lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
row_empno SYS.ANYDATA;
v_empno NUMBER;

BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();


-- Filter out required row and and columns

IF object_owner = 'SCOTT' and
object_name = 'EMP' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN

-- Remove Columns
lcr.delete_column('SAL','*');
lcr.delete_column('JOB','*');

LCR.EXECUTE(TRUE);
END IF;
END;
/

rem Set the DML Handler for the INSERT operations

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


rem Set the DML Handler for the UPDATE operations

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

rem Set the DML Handler for the DELETE operations

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