Modified 10-JUN-2009 Type BULLETIN Status PUBLISHED
PURPOSE
=======
This Note is intended to illustrate how you can rename columns in
the LCR using a DML Apply Handler. The illustration is by example.
You may want to do this when you are capturing and applying between
tables of different structures. In this case, the apply site has
table column names which are not present at the capture site table.
Alternatively, this can also be achieved by using a Transformation
Function at the capture or apply site.
SCOPE & APPLICATION
===================
To be used by DBA's as a reference when configuring and implementing
the Oracle Streams apply process for table level replication.
The example uses the EMP table created in user Scott schema.
The EMP table can be setup by running:
$ORACLE_HOME/sqlplus/demo/demobld.sql
The Apply DML handler renames the EMP table column
HIREDATE to START_DATE for the apply process.
NOTE: Unconditional supplemental logging must be
turned on for HIREDATE at the source site.
The source site EMP table:
Name Null? Type
------------ -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
The destination site EMP table:
SQL> alter table emp rename column HIREDATE to START_DATE;
Name Null? Type
------------ -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
START_DATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
APPLY DML HANDLER EXAMPLE
=========================
rem Create DML handler procedure
CREATE OR REPLACE PROCEDURE rename_column_hiredate (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() = 'SCOTT' AND lcr.get_object_name() = 'EMP' THEN
IF (lcr.get_value('NEW','HIREDATE') is not null) THEN
lcr.rename_column('HIREDATE','START_DATE','NEW');
END IF;
IF (lcr.get_value('OLD','HIREDATE') is not null) THEN
lcr.rename_column('HIREDATE','START_DATE','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_HIREDATE',
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_HIREDATE',
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_HIREDATE',
apply_database_link=> NULL);
END;
/
rem Configure the apply process
rem See Oracle9i Supplied PL/SQL Packages and Types Reference Manual
rem Table 73-8
rem for a full description of the parameters
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(table_name => 'SCOTT.EMP',
streams_type => 'APPLY',
streams_name => '',
queue_name => '',
include_dml => TRUE,
include_ddl => FALSE,
source_database=> '');
END;
/
rem Apply DML Handler Information
column apply_database_link format a20
column user_procedure format a30
column object_name format a15
SELECT object_name,operation_name,user_procedure,
error_handler,apply_database_link
FROM dba_apply_dml_handlers;
10g and 11g Declarative rule-based Transformations
==================================================
With 10g and 11g this can be achieved more easily using
Declarative Rule-Based Transformations.
The following types are possible:
ADD COLUMN, DELETE COLUMN, RENAME COLUMN, RENAME SCHEMA, and RENAME TABLE
When you run one of these procedures to add a transformation,
you specify the rule that is associated with the declarative rule-based
transformation. When the specified rule evaluates to TRUE for a row LCR,
Streams performs the declarative transformation internally on the row LCR,
without invoking PL/SQL.
Declarative rule-based transformations provide the following advantages:
Performance is improved because the transformations are run internally
without using PL/SQL.
Complexity is reduced because custom PL/SQL functions are not required.
For example, a declarative rule-based transformation created by running
the following procedure:
BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'departments33',
table_name => 'hr.departments',
from_column_name => 'manager_id',
to_column_name => 'lead_id',
value_type => 'NEW',
step_number => 0,
operation => 'ADD');
END;
/
To remove a declarative rule-based transformation from a rule, use the
same procedure used to add the transformation, but specify REMOVE for
the operation parameter. For example,
BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'departments33',
table_name => 'hr.departments',
from_column_name => 'manager_id',
to_column_name => 'lead_id',
value_type => 'NEW',
step_number => 0,
operation => 'REMOVE');
END;
/
For more information, please refer to:
10g Streams Concepts and Administration manual
Chapter 7 Rule-Based Transformations
11g Streams Concepts and Administration manual
Chapter 7 Rule-Based Transformations
RELATED DOCUMENTS
=================
Oracle9i Streams Release 2 (9.2)
Oracle9i Supplied PL/SQL Packages and Types Reference Manual
Steps To Setup 9.2x Replication Using Oracle Streams
How To Setup One-Way SCHEMA Level Streams Replication
How To Transform Schema Name In Streams
Combining Data From Multiple Tables Into A
Single LCR For Streams Replication
Example Streams Apply DML Handler Showing Rows
and Columns Filter from the Apply Process
How To Exclude Specific DML Operations From The
Capture Process When Using Streams
How To Exclude A Table From Schema Capture And
Replication When Using Schema Level Streams Replication
Streams Supported and Unsupported Datatypes
(ORA-902 Invalid Datatype)
