Streams Update DML handler

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:

select TIMESTAMP, COMMAND_TYPE, OBJECT_NAME, TRANSACTION_ID, COMMIT_SCN, TX_NAME from  ny4.hist_upd
/
TIMESTAMP COMMAND_TYPE Table Name   TRANSACTIO COMMIT_SCN TX_NAME
--------- ------------ ------------ ---------- ---------- --------
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:

 

BEGIN
  -- 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
    lcr.SET_COMMAND_TYPE('INSERT');
    -- Set the object_name in the row LCR to EMP_DEL
    lcr.SET_OBJECT_NAME('hist_deletes');
    -- Set the object_name in the row LCR to EMP_DEL
    lcr.SET_OBJECT_OWNER('NY4');
    -- 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',

 

                  SYS.AnyData.ConvertDate(SYSDATE));

   -- Apply row LCR as an INSERT into the NY4.hist_deletes table
    lcr.EXECUTE(true);
  END IF;
END;

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
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
 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);
END;
/