Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Streams DML Handlers

Oracle Tips by Burleson 

Oracle Streams
Chapter 6 -
Apply Handlers

DML Handlers

A user procedure that processes row LCRs resulting from DML statements is called a DML handler. An Apply process can have many DML handlers. Why are these important? It is because these handlers provide a mechanism to use the LCR information and execute custom operations on the database tables. There are many ways in which LCR information can be utilized. One instance would be to make changes to the payload and insert them at the destination or develop some kind of audit trail.

The Error handler is also a DML handler. In case of the Error handler, the parameter error_handler is set to TRUE.  In this case, the specified user procedure is run when a row LCR involving the specified operation on the specified object creates an Apply process error. The user procedure may try to resolve possible error conditions, or it may simply notify administrators of the error or it may log the error.

For each table associated with an Apply process, a separate DML handler can be set to process each of the following types of operations in row LCRs:

  • INSERT
     

  • UPDATE
     

  • DELETE
     

  • LOB_UPDATE

For example, the NY4.ALLINSURED1 table may have one DML handler to process INSERT operations, a different DML handler to process UPDATE operations, and yet another for DELETE operations.

A user procedure that processes row LCRs resulting from DML statements is called a DML handler. An Apply process can have many DML handlers. Why are these important? It is because these handlers provide a mechanism to use the LCR information and execute custom operations on the database tables. There are many ways in which LCR information can be utilized. One instance would be to make changes to the payload and insert them at the destination or develop some kind of audit trail.

The Error handler is also a DML handler. In case of the Error handler, the parameter error_handler is set to TRUE.  In this case, the specified user procedure is run when a row LCR involving the specified operation on the specified object creates an Apply process error. The user procedure may try to resolve possible error conditions, or it may simply notify administrators of the error or it may log the error.

For each table associated with an Apply process, a separate DML handler can be set to process each of the following types of operations in row LCRs:

  • INSERT
     

  • UPDATE
     

  • DELETE
     

  • LOB_UPDATE

For example, the NY4.ALLINSURED1 table may have one DML handler to process INSERT operations, a different DML handler to process UPDATE operations, and yet another for DELETE operations.

 

DML Handlers

Figure 6.2 shows the signature of the procedure used to set the DML handler.

Figure 6.2 Package Procedure to set the DML Handler

 

The next section will present information on the DML handlers.

Update DML handler

The update handler is used to track the update transactions and to extend the Apply process functionality. It acts on the update transactions. In the following example, selected records will be updated on the source database table, and the propagation to destination database table will be shown. The Apply process that dequeues the transaction is set up with an Update DML Handler.

Figure 6.3 Setting Up of Update Apply Handler

 

Figure 6.3 shows all of the components involved in setting up a typical DML Update handler.

In the presentation of details of the DML handler setup methodology, the following assumptions should be made:

Source Table                : NY4.allinsured1 at DNYTST10 database
Source DB Capture Proc            : NY4_CAPTURE
Destination Table    : NY4.allinsured1 at DNYOIP20 database
Source DB Apply Proc            : N4_APPLY

 

In order to show the use of the Update DML handler, a stored procedure called NY4.WRITE_UPD_LCRS has been created and the Update DML handler has been set up using this procedure. A new table named NY4.HIST_UPD has been created at DNYOIP20 where the DML handler writes the details about the update operation.

The following SQL statements show the creation and setting up of the Update DML handler:

-- Create a Table to record Update DML history (at DNYOIP20)

CREATE TABLE NY4.hist_upd (
 timestamp              DATE,
 source_database_name   VARCHAR2(40),
 command_type           VARCHAR2(30),
 object_owner           VARCHAR2(32),
 object_name            VARCHAR2(32),
 tag                    RAW(10),
 transaction_id         VARCHAR2(10),
 scn                    NUMBER,
 commit_scn             NUMBER,
 username               varchar2(30),
 tx_name                varchar2(20),
 source_session#        number(6),
 old_values             SYS.LCR$_ROW_LIST,
 new_values             SYS.LCR$_ROW_LIST)
NESTED TABLE old_values STORE AS hist_upd_old_ntable
NESTED TABLE new_values STORE AS hist_upd_new_ntable
/

 

-- Now create a stored procedure (at DNYOIP20)
CREATE OR REPLACE PROCEDURE ny4.write_upd_lcrs(
in_any IN SYS.ANYDATA)
IS
 lcr SYS.LCR$_ROW_RECORD;
 rc PLS_INTEGER;
 
blk_user       varchar2(30) ;
 blk_txname     varchar2(20) ;
 blk_sid        number(6) ;
 ext_attr        SYS.AnyData;
 res             NUMBER;
BEGIN
-- First Access the LCR ; Object is placed in LCR variable
rc       := in_any.GETOBJECT(lcr); 
-- from LCR variable, Anydata is placed in ext_attr variablee
ext_attr := lcr.GET_EXTRA_ATTRIBUTE('username') ;
res      := ext_attr.GETVARCHAR2(blk_user);
ext_attr := lcr.GET_EXTRA_ATTRIBUTE('tx_name') ;
res      := ext_attr.GETVARCHAR2(blk_txname);
ext_attr := lcr.GET_EXTRA_ATTRIBUTE('session#') ;
res      := ext_attr.GETNUMBER(blk_sid);

 

-- Insert info about the LCR into the hist_upd table
INSERT INTO ny4.hist_upd 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,
 blk_user,
 
blk_txname,
 blk_sid,
 lcr.GET_VALUES('old'),
 lcr.GET_VALUES('new', 'n') );
-- Apply row LCR
lcr.EXECUTE(true);
END;
/

From the above procedure, it is apparent that the old and new values of updated columns besides the other details can be saved into the table, NY4.HIST_UPD. Every update transaction is recorded into the table. It also records some additional attributes such as username, transaction name, etc.

The next step is to set up the Update DML Handler using the following statement:

-- To set up the DML Handler
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
 object_name      => 'ny4.allinsured1',
 object_type      => 'TABLE',
 operation_name   => 'UPDATE',
 error_handler    => false,
 
user_procedure   => 'ny4.write_upd_lcrs',
 apply_database_link => NULL,
 apply_name => NULL);
END;
/

To verify the Apply handlers, execute the SQL below:

SELECT OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME, USER_PROCEDURE
 FROM DBA_APPLY_DML_HANDLERS ORDER BY OBJECT_OWNER, OBJECT_NAME
/

 

Table                                                   
Owner    Table Name     Operation  Handler Procedure           
-------- ----------    ----------- ---------------------
NY4      ALLINSURED1    UPDATE     "NY4"."WRITE_UPD_LCRS"
NY4      ALLINSURED1    DELETE     "NY4"."WRITE_DEL_LCRS"

To unset the DML handler, set the user_procedure parameter to NULL. For example, use the following SQL Statement to unset the apply handler:

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
 object_name      => 'ny4.allinsured1',
 object_type      => 'TABLE',
 operation_name   => 'UPDATE',
 error_handler    => false,
 
user_procedure   => NULL ,
 
apply_database_link => NULL,
 apply_name => NULL);
END;
/

 

To verify the extra attributes included, the following SQL can be used:

DNYTST10 SQL>@show_capture_extra_attrib
 
Capture Process      Attribute Name  Include Attrib in LCRs
-------------------- --------------- -----------------------
NY4_CAPTURE          THREAD#         NO
NY4_CAPTURE          ROW_ID          NO
NY4_CAPTURE          SESSION#        YES
NY4_CAPTURE          SERIAL#         NO
NY4_CAPTURE          USERNAME        YES
NY4_CAPTURE          TX_NAME         YES

6 rows selected.

Next, the transaction name at source database is set before an update operation is executed. In the following example, the transaction name is set to my_txn10:

DNYTST10 SQL>SET TRANSACTION NAME 'my_txn10' ;

Transaction set.

 

At the source database, some extra attributes have been included in the Capture process called NY4_CAPTURE. Use the following SQL statements:

-- AT CAPTURE side, include some Extra Attributes

BEGIN
DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
capture_name => 'NY4_CAPTURE', attribute_name => 'tx_name',
include => true);
END;
/
BEGIN
DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
capture_name => 'NY4_CAPTURE', attribute_name => 'username',
include => true);
END;
/
BEGIN
DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
capture_name => 'NY4_CAPTURE', attribute_name => 'session#',
include => true);
END;
/

 

The next step is to run the following SQL statements to execute Update DML at the source database table:

-- connect strmadm/strmadm@dnytst10
DNYTST10 SQL>update ny4.allinsured1 set premium = 10
where pol_no = 15000 and SALES_ID = 174 ;

1 row updated.

DNYTST10 SQL>commit ;

Commit complete.
-- After update
DNYOIP20 SQL>select pol_no, sales_id, premium from ny4.allinsured1 where
pol_no = 15000 ;

    POL_NO   SALES_ID    PREMIUM
---------- ---------- ----------
     15000        174         10

 

 


The above text is an excerpt from:

Oracle Streams
High Speed Replication and Data Sharing

ISBN 0-9745993-5-2

by Madhu Tumma
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks