Streams DML 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.

 

 

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