Oracle Streams Rename Column

Oracle Streams Rename Column.

Source Schame Name : EMP

Source Table Name : MANAGER

Column Names : MANAGER_ID,MANAGER_NAME,EMPLOYEE

Target Schame Name : EMP

Target Table Name : MANAGER

Column Names : MANAGER_ID,MANAGER_NAME,WORKER

Two Steps.

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


1.Rule-based transformation.

Create Capture rule.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
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='MANAGER';


BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'MANAGER33',
table_name => 'EMP.MANAGER',
from_column_name => 'EMPLOYEE',
to_column_name => 'WORKER',
value_type => 'NEW',
step_number => 0,
operation => 'ADD');
END;
/


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

CREATE OR REPLACE PROCEDURE rename_column_EMPLOYEE (in_any in sys.anydata)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(30);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();

-- Filter out required owner and table name
IF lcr.get_object_owner() = 'EMP' AND lcr.get_object_name() = 'MANAGER' THEN
IF (lcr.get_value('NEW','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','NEW');
END IF;
IF (lcr.get_value('OLD','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','OLD');
END IF;
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.RENAME_COLUMN_EMPLOYEE',
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.RENAME_COLUMN_EMPLOYEE',
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.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/