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

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Streams Update DML handler

Oracle Tips by Burleson 

Oracle Streams
Chapter 6 -
Apply Handlers

Update DML handler

The transaction is then propagated and applied to the destination table by the procedure specified as the DML handler. The procedure, besides inserting into the hist_upd table, executes the LCR event ( lcr.EXECUTE(true)  ).

The statement below shows the entry added to the hist_upd table at DNYOIP20 by way of the DML handler procedure NY4.WRITE_UPD_LCRS:

--------- ------------ ------------ ---------- ---------- --------
30-OCT-04 UPDATE       ALLINSURED1  3.44.16636   14324527 MY_TXN10

The hist_upd table, which is written by the DML Handler procedure, provides a record of all the updates to the base table.

The next step is to examine the handlers for delete DML operations.

Delete DML Operation

There are some situations where deleting records may need to be controlled. Accidental deletes need to be avoided. If a deletion takes place, the DBA may not want to reflect the deletion at the destination and may want to record the details. This is where the Delete DML Handler would be very handy.


In this example, a Delete DML handler that manipulates the incoming LCR event will be created at the destination. Whenever a delete transaction arrives to the destination queue, the LCR is modified to write to an alternate table with an additional column. The Apply process that dequeues the transaction is set up with a Delete DML handler.

Figure 6.4 Setting up of Delete DML Handler


Figure 6.4 shows all of the components involved in setting up a typical Delete DML handler.

Next, information will be presented on the DML delete handler setup methodology. The following are the assumptions:

Source Table                : ny4.allinsured1 at DNYTST10 database
Source DB Capture Proc            : NY4_CAPTURE
Destination Table    : ny4.allinsured1 at DNYOIP20 database
Alternate Table             : ny4.hist_deletes at DNYOIP20 database
Destination Apply Proc            : LN4_APPLY

In order to show the use of Delete DML handler, a stored procedure named NY4.WRITE_DEL_LCRS has been created, and the Delete DML handler has been set up. A new table named NY4.HIST_DELETES has been created at DNYOIP20.  This table is where the DML handler writes the details about the delete operation.

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


  -- Access the LCR
  rc := in_any.GETOBJECT(lcr);

  -- Get the object command type
  command := lcr.GET_COMMAND_TYPE();

  -- Check for DELETE command on the ny4.allinsured1_del table
  IF command = 'DELETE' THEN
    -- Set the command_type in the row LCR to INSERT
    -- Set the object_name in the row LCR to EMP_DEL
    -- Set the object_name in the row LCR to EMP_DEL
    -- Get the old values in the row LCR
    old_values := lcr.GET_VALUES('old');
    -- Set the old val of row LCR to new values of row LCR
    lcr.SET_VALUES('new', old_values);
    -- Set the old values in the row LCR to NULL
    lcr.SET_VALUES('old', NULL);
    -- Add a SYSDATE value for the timestamp column
    lcr.ADD_COLUMN('new', 'time_of_dml',



   -- Apply row LCR as an INSERT into the NY4.hist_deletes table

In the above procedure, the LCR event has been altered as follows:

  • the command type has been changed to INSERT

  • the object_name has been changed to hist_deletes

  • the command type has been changed to NY4

  • a new column named TIME_OF_DML has been added and a value of SYSDATE has been assigned to it

The next step is the execution of the LCR event. This has the effect of the inserting to a new table called NY4.HIST_DELETES instead of the originally intended ALLINSURED1 table.

The procedure can be assigned as the DML Delete Handler by executing the following SQL statement:

-- To Assign Delete DML Handler, use the following SQL block
 object_name      => 'ny4.allinsured1',
 object_type      => 'TABLE',
 operation_name   => 'DELETE',
 error_handler    => false,
user_procedure   => 'ny4. write_del_lcrs',
 apply_database_link => NULL,
 apply_name => NULL);



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:

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