Example Streams Apply DML Handler Showing the Adding of Columns to the Apply LCR [ID 265867.1]
Modified 10-JUN-2009 Type BULLETIN Status PUBLISHED
PURPOSE
=======
This Note is intended to illustrate how you can add columns to 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 columns
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. You should also refer to Streams Supported and
Unsupported Datatypes (ORA-902 Invalid Datatype)
REFERENCES
==========
10.1 Streams Replication Administrator's manual
Chapter 1 Understanding Streams Replication
(10.2 Streams Replication Administrator's Guide manual
Chapter 1 Understanding Streams Replication)
Apply Process Behavior for Column Discrepancies
A column discrepancy is any difference in the columns
in a table at a source database and the columns in
the same table at a destination database. If there are
column discrepancies in your Streams environment, then
use rule-based transformations or DML handlers to make
the columns in row LCRs being applied by an apply process
match the columns in the relevant tables at a destination
database
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 dept table create in user Scott schema.
The dept table can be setup by running:
$ORACLE_HOME/sqlplus/demo/demobld.sql
The Apply DML handler adds the dept table columns
MANAGER_ID and MANAGER_NAME to the apply process.
The source site DEPT table:
Name Null? Type
----------------------------- -------- ---------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
The destination site DEPT table:
Name Null? Type
----------------------------- -------- ---------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
MANAGER_ID NUMBER(4)
MANAGER_NAME VARCHAR2(12)
APPLY DML HANDLER EXAMPLE
=========================
rem Create DML handler procedure
CREATE OR REPLACE PROCEDURE dept_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);
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();
IF object_owner = 'SCOTT' and
object_name = 'DEPT' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN
-- Add Columns
lcr.add_column('NEW','MANAGER_ID',sys.anydata.convertnumber(123));
lcr.add_column('NEW','MANAGER_NAME',sys.anydata.convertvarchar2('JONES'));
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.DEPT',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.DEPT_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.DEPT',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.DEPT_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.DEPT',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.DEPT_DML_HANDLER',
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.DEPT',
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;
Further Examples
================
There are further examples of DML and DDL Apply Handlers in the
Streams Manuals.
Another example of a DML Handler being used to record the history of
DML changes. In this case, a DML handler can insert information about
an LCR it processes into a table and then apply the LCR using the
EXECUTE member procedure. To create such a DML handler, first create
a table to hold the history information:
CREATE TABLE strmadmin.history_row_lcrs(
timestamp DATE,
source_database_name VARCHAR2(128),
command_type VARCHAR2(30),
object_owner VARCHAR2(32),
object_name VARCHAR2(32),
tag RAW(10),
transaction_id VARCHAR2(10),
scn NUMBER,
commit_scn NUMBER,
old_values SYS.LCR$_ROW_LIST,
new_values SYS.LCR$_ROW_LIST)
NESTED TABLE old_values STORE AS old_values_ntab
NESTED TABLE new_values STORE AS new_values_ntab;
CREATE OR REPLACE PROCEDURE history_dml(in_any IN ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
-- Insert information about the LCR into the history_row_lcrs table
INSERT INTO strmadmin.history_row_lcrs VALUES
(SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(),
lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_TAG(),
lcr.GET_TRANSACTION_ID(), lcr.GET_SCN(), lcr.GET_COMMIT_SCN,
lcr.GET_VALUES('old'), lcr.GET_VALUES('new', 'n'));
-- Apply row LCR
lcr.EXECUTE(TRUE);
END;
/
In this example, the apply_name parameter is set to NULL.
Therefore, the DML handler is a general DML handler that is used
by all of the apply processes in the database.
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'hr.locations',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'strmadmin.history_dml',
apply_database_link => NULL,
apply_name => NULL);
END;
/
For further information, please refer to the
11g Streams Replication Administrator's Guide
Chapter 9 Managing Capture, Propagation, and Apply
Managing a DML Handler
RELATED DOCUMENTS
=================
Oracle9i Streams Release 2 (9.2)
10.1 Streams Replication Administrator's manual
Chapter 1 Understanding Streams Replication
10.2 Streams Replication Administrator's Guide manual
Chapter 1 Understanding Streams Replication
Oracle9i Supplied PL/SQL Packages and Types Reference Manual
11g Streams Replication Administrator's Guide
Chapter 9 Managing Capture, Propagation, and Apply
Streams Supported and Unsupported Datatypes
(ORA-902 Invalid Datatype)
Combining Data From Multiple Tables Into A Single LCR
For Streams Replicatio
Example Streams Apply DML Handler Showing Rows
and Columns Filter from the Apply Process
Steps To Setup 9.2x Replication Using Oracle Streams
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
