Example Streams Apply DML Handler Showing Rows and Columns Filter from the Apply Process [ID 265481.1]
Modified 28-MAY-2009 Type BULLETIN Status PUBLISHED
PURPOSE
=======
This Note is intended to illustrate how you can exclude or filter specific
table rows and columns data from the apply process using a DML Apply Handler.
The illustration is by example. Alternatively, this can also be achieved by
using a Transformation Function at the capture or apply site.
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 emp table create in user Scott schema.
The emp table can be setup by running:
$ORACLE_HOME/sqlplus/demo/demobld.sql
The DML handler excludes the emp table columns SAL, JOB and DEPTNO
from the apply process. It also excludes all emp table rows with an
EMPNO < 7900
APPLY DML HANDLER EXAMPLE
=========================
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();
row_empno := lcr.GET_VALUE('NEW','EMPNO');
rc := row_empno.getnumber(v_empno);
-- Filter out required row and and columns
IF object_owner = 'SCOTT' and
object_name = 'EMP' and
v_empno >= 7900 and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN
-- Remove Columns
lcr.delete_column('SAL','*');
lcr.delete_column('JOB','*');
lcr.delete_column('DEPTNO','*');
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;
/
rem Configure the apply process
rem See Oracle 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;
rem If the apply_user is not the owner of the dml handler make sure
rem that you grant execute on the apply handler to the apply_user
rem to prevent any permission related errors. See for
rem more information
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
9i Supplied PL/SQL Packages and Types Reference Manual
11g Streams Concepts and Administration
11g Streams Replication Administrator's Guide
Steps To Setup 9.2x Replication Using Oracle Streams
Example Streams Apply DML Handler Showing LCR Column Rename
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
Example Streams Apply DML Handler Showing the Adding of
Columns to the Apply LCR
Combining Data From Multiple Tables Into A Single LCR
For Streams Replication
Streams Apply Process Aborts With ORA-6550
