Example Streams set_dml_handler(4)

Example Streams Apply DML Handler to change the datatype of a column in the Apply LCR [ID 865634.1]
		Modified 30-JUL-2009     Type BULLETIN     Status PUBLISHED

PURPOSE
=======
This Note is intended to illustrate how you can change the datatype
of a column or columns using a DML Apply Handler.  In the example, the
table at the source DB has 2 TIMESTAMP columns and the corresponding
columns at the TARGET database are of DATE datatype.
 
REFERENCES
==========
10.2 Streams Replication Administrator's Guide manual

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 table T1 in schema USR1.  The table has the following 
structure on each database:

Source:

SQL> desc usr1.t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                         NOT NULL NUMBER
 M                                                  NUMBER
 C                                                  VARCHAR2(20)
 D                                                  TIMESTAMP(6)
 E                                                  TIMESTAMP(6)

Target:

SQL> desc usr1.t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                         NOT NULL NUMBER
 M                                                  NUMBER
 C                                                  VARCHAR2(20)
 D                                                  DATE
 E                                                  DATE

The Apply DML handler changes the datatype of columns D and E from TIMESTAMP
to DATE for INSERT operations:  


APPLY DML HANDLER EXAMPLE
=========================

conn strmadmin/strmadmin
rem Create DML handler procedure
CREATE OR REPLACE PROCEDURE change_timestamp (in_any in sys.anydata)
IS
lcr           SYS.LCR$_ROW_RECORD;
rc            PLS_INTEGER;
ov2           sys.lcr$_row_list;
t             timestamp;
d             date;
i             number;
ret           pls_integer;
BEGIN
   -- Access the LCR
   rc := in_any.GETOBJECT(lcr);
   ov2 := lcr.get_values ('NEW');
   for i in 1 .. ov2.count loop
      if lcr.get_object_name() = 'T1' and lcr.get_object_owner() = 'USR1' then
         if ov2(i).column_name = 'D' or ov2(i).column_name = 'E' then
           ret :=  ov2(i).DATA.GetTimestamp(t);
           d := t;
           lcr.delete_column(ov2(i).column_name);
           lcr.add_column('NEW',ov2(i).column_name, Sys.AnyData.ConvertDate(d));
        end if;
      end if;
   end loop;
   lcr.execute(true);
END;
/


rem Set the DML Handler for the INSERT operations

BEGIN 
    DBMS_APPLY_ADM.SET_DML_HANDLER
    (object_name        => 'USR1.T1',
     object_type        => 'TABLE',
     operation_name     => 'INSERT',
     error_handler      => FALSE,
     user_procedure     => 'STRMADMIN.CHANGE_TIMESTAMP',
     apply_database_link=> NULL);
END;
/


For further information, please refer to the 
11g Streams Replication Administrator's Guide
Chapter 9 Managing Capture, Propagation, and Apply
Managing a DML Handler

RELATED DOCUMENTS
=================
10.2 Streams Replication Administrator's Guide manual
Chapter 1 Understanding Streams Replication
Oracle9i Supplied PL/SQL Packages and Types Reference Manual
11g Streams Replication Administrator's Guide
Chapter 9 Managing Capture, Propagation, and Apply
 Streams Supported and Unsupported Datatypes 
                (ORA-902 Invalid Datatype)
 Combining Data From Multiple Tables Into A Single LCR 
                For Streams Replicatio
 Example Streams Apply DML Handler Showing Rows 
                and Columns Filter from the Apply Process
 Steps To Setup 9.2x Replication Using Oracle Streams
 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