User-defined types include object types, REF values, varrays,
and nested tables. Currently, Streams capture processes and apply processes do
not support user-defined types. This section discusses some techniques for
handling user-defined types in a Streams environment. These techniques are
most useful when using Streams to perform a database upgrade or maintenance
operation on a database that uses user-defined types.
One option is to make tables that contain user-defined types read-only during the database maintenance operation. In this case, these tables are instantiated at the destination database, and no changes are made to these tables during the entire operation. After the upgrade or maintenance operation is complete, make the tables that contain user-defined types read-write at the destination database.
However, if tables that contain user-defined types must remain open during the upgrade or maintenance operation, then the following general steps may be used to retain changes to these types during the database maintenance operation:
At the source database, create one or more logging tables to store row changes to tables that include user-defined types. Each column in the logging table must use a datatype that is supported by Streams.
At the source database, create a DML trigger (click here for trigger example) that fires on the tables that contain the user-defined datatypes. The trigger converts each row change into relational equivalents and logs the modified row in a logging table create in Step 1.
At the source database, configure the capture process and propagation to capture and propagate changes to the logging table to the destination database. Changes to tables that contain user-defined types should not be captured or propagated.
At the destination, configure the apply process to use a DML handler that processes the changes to the logging tables. The DML handler reconstructs the user-defined types from the relational equivalents and applies the modified changes to the to the tables that contain user-defined types (click here for handler example ). For the apply process to function properly, the logging tables should exist and have the same structure at the destination database as they do at the source database. However, no changes are applied to the logging tables at the destination database.
Restrictions: (downloadable zip file of this webpage and sql file)
If the user-defined type contains varrays, then the destination platform must have the same character set.
If the user-defined type contains LOB columns (BLOB or CLOB), an additional workaround for handling piece-wise updates to LOBs must be implemented. An ON-COMMIT materialized view is created selecting the key and LOB columns from the table with the user-defined type. On the source database, Streams is configured to capture the changes to this MV table. At the destination, configure a DML Handler on the apply process for this table. The DML Handler is constructed to directly UPDATE the LOB columns using the primary key.
The following is the logfile output from a standalone test case demonstrating replication of a table
containing an ADT. This logfile does not include the additional workaround
for handling piece-wise updates to LOBS. However, the downloadable sql
file (and associated log file) provides the complete solution for handling ADTs.
To prepare for running this sql script, configure two(2) Oracle 10g databases. The source database (where changes are captured) must be Enterprise Edition and enabled for ARCHIVE LOGGING.
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> COLUMN SRC_LONG_CNAME FORMAT A15
SQL> COLUMN SCHEMA_NAME FORMAT A15
SQL> COLUMN OBJECT_NAME FORMAT A15
SQL>
SQL> SET ECHO ON
SQL>
SQL> VARIABLE site1 VARCHAR2(80);
SQL> VARIABLE site2 VARCHAR2(80);
SQL> VARIABLE scn NUMBER;
SQL>
SQL> CONNECT SYS/CHANGE_ON_INSTALL@INST1 AS SYSDBA
Connected.
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX');
PL/SQL procedure successfully completed.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL> CREATE DATABASE LINK DBS2 CONNECT TO stradm IDENTIFIED BY stradm
2 USING 'INST2';
Database link created.
SQL>
SQL> CONNECT stradm/stradm@INST2
Connected.
SQL> CREATE DATABASE LINK DBS1 CONNECT TO stradm IDENTIFIED BY stradm
2 USING 'INST1';
Database link created.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL> EXECUTE SELECT GLOBAL_NAME INTO :site1 FROM GLOBAL_NAME@DBS1;
PL/SQL procedure successfully completed.
SQL> EXECUTE SELECT GLOBAL_NAME INTO :site2 FROM GLOBAL_NAME@DBS2;
PL/SQL procedure successfully completed.
SQL> PRINT site1;
SITE1
--------------------------------------------------------------------------------
DBS1.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> PRINT site2;
SITE2
--------------------------------------------------------------------------------
DBS2.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL>
SQL> CONNECT stradm/stradm@INST2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE( QUEUE_USER => 'stradm') ;
PL/SQL procedure successfully completed.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE( QUEUE_USER => 'stradm') ;
PL/SQL procedure successfully completed.
SQL>
SQL> -- ---------------------------------------------------------------------------
SQL> -- Create apply and propagation rules at inst2
SQL> -- ---------------------------------------------------------------------------
SQL> CONNECT stradm/stradm@INST2
Connected.
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
3 SCHEMA_NAME => 'user1',
4 STREAMS_TYPE => 'APPLY',
5 STREAMS_NAME => 'apply_from_inst1',
6 QUEUE_NAME => 'stradm.STREAMS_QUEUE',
7 INCLUDE_DML => TRUE,
8 INCLUDE_DDL => TRUE,
9 SOURCE_DATABASE => :site1);
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
3 SCHEMA_NAME => 'user1',
4 STREAMS_NAME => 'INST1_TO_INST2',
5 SOURCE_QUEUE_NAME => 'stradm.STREAMS_QUEUE',
6 DESTINATION_QUEUE_NAME => 'stradm.STREAMS_QUEUE@'||:site2,
7 INCLUDE_DML => TRUE,
8 INCLUDE_DDL => TRUE,
9 SOURCE_DATABASE => :site1);
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> -------------------------------------------------.
SQL> -- Create user user1 and tables at inst1
SQL> -------------------------------------------------.
SQL> CONNECT SYSTEM/MANAGER@INST1
Connected.
SQL> DROP USER user1 CASCADE;
DROP USER user1 CASCADE
*
ERROR at line 1:
ORA-01918: user 'USER1' does not exist
SQL> CREATE USER user1 IDENTIFIED BY user1;
User created.
SQL> GRANT CONNECT,RESOURCE TO user1;
Grant succeeded.
SQL>
SQL> CONNECT user1/user1@INST1
Connected.
SQL>
SQL> -- create sdo_geometry types in user1's schema for this test (If the spatial
SQL> -- option was loaded, then these types would already have been created in
SQL> -- the MDSYS schema)
SQL> CREATE TYPE sdo_point_type AS OBJECT (
2 X NUMBER,
3 Y NUMBER,
4 Z NUMBER);
5 /
Type created.
SQL>
SQL> CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
2 /
Type created.
SQL> CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;
2 /
Type created.
SQL>
SQL> CREATE TYPE sdo_geometry AS OBJECT (
2 SDO_GTYPE NUMBER,
3 SDO_SRID NUMBER,
4 SDO_POINT SDO_POINT_TYPE,
5 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
6 SDO_ORDINATES SDO_ORDINATE_ARRAY);
7 /
Type created.
SQL>
SQL> -- create a table using the SDO_GEOMETRY types
SQL> CREATE TABLE cola_markets (
2 mkt_id NUMBER PRIMARY KEY,
3 name VARCHAR2(32),
4 shape SDO_GEOMETRY);
Table created.
SQL>
SQL> -- log of the cola_markets table (relational equivalents)
SQL> -- we will log all the new values and old key column
SQL> CREATE TABLE rel_cola_markets_log(
2 dmltype VARCHAR2(1),
3 o_mkt_id NUMBER,
4 n_mkt_id NUMBER,
5 n_name VARCHAR2(32),
6 n_shape RAW(16),
7 n_shape_gtype NUMBER,
8 n_shape_srid NUMBER,
9 n_shape_sdo_point_x NUMBER,
10 n_shape_sdo_point_y NUMBER,
11 n_shape_sdo_point_z NUMBER,
12 n_shape_sdo_elem_info BLOB,
13 n_shape_sdo_ordinates BLOB);
Table created.
SQL>
SQL> connect sys/change_on_install@inst1 as sysdba
Connected.
SQL> -- get the underlying relational column equivalents of the ADT table
SQL> -- For each ADT column, there will the following columns
SQL> -- a) an underlying relational column with the same name of type RAW
SQL> -- b) underlying relational columns for the leaf atrribute columns and they
SQL> -- are identified by the following query (Note: qry needs to be optimized)
SQL> select attrcol$.name adt_name, col$.name rel_name
2 from col$, attrcol$, obj$, user$
3 where col$.obj# = attrcol$.obj#
4 and col$.intcol# = attrcol$.intcol#
5 and obj$.obj# = col$.obj#
6 and obj$.owner# = user$.user#
7 and user$.name = 'USER1'
8 and obj$.name = 'COLA_MARKETS';
ADT_NAME
--------------------------------------------------------------------------------
REL_NAME
------------------------------
"SHAPE"."SDO_GTYPE"
SYS_NC00004$
"SHAPE"."SDO_SRID"
SYS_NC00005$
"SHAPE"."SDO_POINT"."X"
SYS_NC00006$
"SHAPE"."SDO_POINT"."Y"
SYS_NC00007$
"SHAPE"."SDO_POINT"."Z"
SYS_NC00008$
"SHAPE"."SDO_ELEM_INFO"
SYS_NC00009$
"SHAPE"."SDO_ORDINATES"
SYS_NC00010$
7 rows selected.
SQL>
SQL> connect user1/user1@inst1
Connected.
SQL> -- create a trigger which will log dml into rel_cola_markets_log
SQL> -- This trigger will insert a row into rel_cola_markets_log for every dml
SQL> -- that occurs to cola_markets and will immediately delete the row. The row
SQL> -- changes (insert and delete) will be captured and propagated to the
SQL> -- destination, where the insert row record will be processed by the apply
SQL> -- dml handler and the corressponding dml will be appled to cola_markets
SQL> -- table. The deletes to rel_cola_markets_log will be discarded by the
SQL> -- apply handler.
SQL> create or replace trigger sdo_trigger
2 after delete or insert or update on cola_markets for each row
3 declare
4 l_rowid rowid;
5 begin
6 if inserting then
7 insert
8 into rel_cola_markets_log(dmltype,
9 o_mkt_id,
10 n_mkt_id,
11 n_name,
12 n_shape,
13 n_shape_gtype,
14 n_shape_srid,
15 n_shape_sdo_point_x,
16 n_shape_sdo_point_y,
17 n_shape_sdo_point_z,
18 n_shape_sdo_elem_info,
19 n_shape_sdo_ordinates)
20 values('I', NULL, :new.mkt_id, :new.name,
21 SYS_OP_NII(:new.shape, 3),
22 :new.sys_nc00004$,
23 :new.sys_nc00005$, :new.sys_nc00006$, :new.sys_nc00007$,
24 :new.sys_nc00008$,
25 SYS_ET_IMAGE_TO_BLOB(:new.sys_nc00009$),
26 SYS_ET_IMAGE_TO_BLOB(:new.sys_nc00010$))
27 returning rowid into l_rowid;
28 elsif updating then
29 insert
30 into rel_cola_markets_log(dmltype,
31 o_mkt_id,
32 n_mkt_id,
33 n_name,
34 n_shape,
35 n_shape_gtype,
36 n_shape_srid,
37 n_shape_sdo_point_x,
38 n_shape_sdo_point_y,
39 n_shape_sdo_point_z,
40 n_shape_sdo_elem_info,
41 n_shape_sdo_ordinates)
42 values('U', :old.mkt_id, :new.mkt_id, :new.name,
43 SYS_OP_NII(:new.shape, 3),
44 :new.sys_nc00004$,
45 :new.sys_nc00005$, :new.sys_nc00006$, :new.sys_nc00007$,
46 :new.sys_nc00008$,
47 SYS_ET_IMAGE_TO_BLOB(:new.sys_nc00009$),
48 SYS_ET_IMAGE_TO_BLOB(:new.sys_nc00010$))
49 returning rowid into l_rowid;
50 elsif deleting then
51 insert into rel_cola_markets_log(dmltype, o_mkt_id)
52 values('D', :old.mkt_id)
53 returning rowid into l_rowid;
54 end if;
55
56 -- delete the rows from rel_cola_markets_log
57 delete rel_cola_markets_log where rowid = l_rowid;
58 end;
59 /
Trigger created.
SQL>
SQL>
SQL> -------------------------------------------------.
SQL> -- Create user user1 and tables at inst2
SQL> -------------------------------------------------.
SQL> CONNECT SYSTEM/MANAGER@INST2
Connected.
SQL> DROP USER user1 CASCADE;
DROP USER user1 CASCADE
*
ERROR at line 1:
ORA-01918: user 'USER1' does not exist
SQL> CREATE USER user1 IDENTIFIED BY user1;
User created.
SQL> GRANT CONNECT,RESOURCE TO user1;
Grant succeeded.
SQL>
SQL> connect sys/change_on_install@inst2 as sysdba
Connected.
SQL> grant all on dbms_system to user1;
Grant succeeded.
SQL>
SQL> CONNECT user1/user1@INST2
Connected.
SQL>
SQL> -- create sdo_geometry types in user1's schema for this test (If the spatial
SQL> -- option was loaded, then these types would already have been created in
SQL> -- the MDSYS schema)
SQL> CREATE TYPE sdo_point_type AS OBJECT (
2 X NUMBER,
3 Y NUMBER,
4 Z NUMBER);
5 /
Type created.
SQL>
SQL> CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
2 /
Type created.
SQL> CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;
2 /
Type created.
SQL>
SQL> CREATE TYPE sdo_geometry AS OBJECT (
2 SDO_GTYPE NUMBER,
3 SDO_SRID NUMBER,
4 SDO_POINT SDO_POINT_TYPE,
5 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
6 SDO_ORDINATES SDO_ORDINATE_ARRAY);
7 /
Type created.
SQL>
SQL> -- create cola_markets table
SQL> CREATE TABLE cola_markets (
2 mkt_id NUMBER PRIMARY KEY,
3 name VARCHAR2(32),
4 shape SDO_GEOMETRY)
5 varray shape.sdo_ordinates store as lob shape_sdo_ordinates
6 varray shape.sdo_elem_info store as lob shape_sdo_elem_info;
Table created.
SQL>
SQL> -- needed for dummy purposes only (awaiting a 10i bug fix)
SQL> -- so that we can define an apply dml handler
SQL> CREATE TABLE rel_cola_markets_log(
2 dmltype VARCHAR2(1),
3 o_mkt_id NUMBER,
4 n_mkt_id NUMBER,
5 n_name VARCHAR2(32),
6 n_shape RAW(16),
7 n_shape_gtype NUMBER,
8 n_shape_srid NUMBER,
9 n_shape_sdo_point_x NUMBER,
10 n_shape_sdo_point_y NUMBER,
11 n_shape_sdo_point_z NUMBER,
12 n_shape_sdo_elem_info BLOB,
13 n_shape_sdo_ordinates BLOB);
Table created.
SQL>
SQL>
SQL> CONNECT user1/user1@INST1
Connected.
SQL> CREATE DATABASE LINK DBS2 CONNECT TO user1 IDENTIFIED BY user1
2 USING 'INST2';
Database link created.
SQL>
SQL> CONNECT user1/user1@INST2
Connected.
SQL> CREATE DATABASE LINK DBS1 CONNECT TO user1 IDENTIFIED BY user1
2 USING 'INST1';
Database link created.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL>
SQL> -- CREATE CAPTURE PROCESS AT INST1 and add rules to allow dml/ddl changes
SQL> -- to rel_cola_markets_logs to be captured
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 TABLE_NAME => 'user1.rel_cola_markets_log',
4 STREAMS_TYPE => 'capture',
5 STREAMS_NAME => 'capture_user1',
6 QUEUE_NAME => 'stradm.STREAMS_QUEUE',
7 INCLUDE_DML => TRUE,
8 INCLUDE_DDL => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT CAPTURE_NAME, QUEUE_NAME, QUEUE_OWNER, STATUS
2 FROM DBA_CAPTURE@DBS1 ORDER BY 1,2;
CAPTURE_NAME QUEUE_NAME
------------------------------ ------------------------------
QUEUE_OWNER STATUS
------------------------------ --------
CAPTURE_USER1 STREAMS_QUEUE
STRADM DISABLED
1 row selected.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL> EXEC :scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
PL/SQL procedure successfully completed.
SQL>
SQL> -- set instantiation scns for rel_cola_market_logs
SQL> CONNECT stradm/stradm@INST2
Connected.
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (
3 'user1.rel_cola_markets_log',:site1,:scn);
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> connect user1/user1@inst2
Connected.
SQL>
SQL> -- helper function for apply dml handler
SQL> create or replace function my_getvarchar(lcr in sys.lcr$_row_record,
2 cname in varchar2)
3 return varchar2 is
4 ad sys.anydata;
5 ret_val varchar2(4000) := NULL;
6 dummy pls_integer;
7 begin
8 ad := lcr.get_value('NEW', cname);
9 if (ad is not null) then
10 dummy := ad.getvarchar2(ret_val);
11 end if;
12 return ret_val;
13 end;
14 /
Function created.
SQL>
SQL> -- helper function for apply dml handler
SQL> create or replace function my_getraw(lcr in sys.lcr$_row_record,
2 cname in varchar2)
3 return raw is
4 ad sys.anydata;
5 ret_val raw(4000) := NULL;
6 dummy pls_integer;
7 begin
8 ad := lcr.get_value('NEW', cname);
9 if (ad is not null) then
10 dummy := ad.getraw(ret_val);
11 end if;
12 return ret_val;
13 end;
14 /
Function created.
SQL>
SQL> -- helper function for apply dml handler
SQL> create or replace function my_getnumber(lcr in sys.lcr$_row_record,
2 cname in varchar2)
3 return number is
4 ad sys.anydata;
5 ret_val number := NULL;
6 dummy pls_integer;
7 begin
8 ad := lcr.get_value('NEW', cname);
9 if (ad is not null) then
10 dummy := ad.getnumber(ret_val);
11 end if;
12 return ret_val;
13 end;
14 /
Function created.
SQL>
SQL> -- helper procedure for apply dml handler
SQL> create or replace function user1.my_getblob(lcr in sys.lcr$_row_record,
2 cname in varchar2,
3 lob_loc in out nocopy blob)
4 return boolean is
5 lobinfo pls_integer;
6 raw_val raw(4000);
7 lobamt number;
8 tmp_lob_created boolean := FALSE;
9 begin
10 lobinfo := lcr.get_lob_information('NEW', cname, 'N');
11
12 if (lobinfo = dbms_lcr.null_lob) then
13 lob_loc := NULL;
14 elsif (lobinfo = dbms_lcr.empty_lob) then
15 lob_loc := empty_blob();
16 else
17 raw_val := my_getraw(lcr, cname);
18 if (raw_val is not null) then
19 lobamt := utl_raw.length(raw_val);
20 dbms_lob.createtemporary(lob_loc, true, dbms_lob.session);
21 dbms_lob.writeappend(lob_loc, lobamt, raw_val);
22 tmp_lob_created := TRUE;
23 end if;
24 end if;
25 return tmp_lob_created;
26 end;
27 /
Function created.
SQL>
SQL>
SQL> connect sys/change_on_install@inst2 as sysdba
Connected.
SQL> -- get the underlying relational column equivalents of the ADT table
SQL> -- For each ADT column, there will the following columns
SQL> -- a) an underlying relational column with the same name of type RAW
SQL> -- b) underlying relational columns for the leaf atrribute columns and they
SQL> -- are identified by the following query (Note: qry needs to be optimized)
SQL> select attrcol$.name adt_name, col$.name rel_name
2 from col$, attrcol$, obj$, user$
3 where col$.obj# = attrcol$.obj#
4 and col$.intcol# = attrcol$.intcol#
5 and obj$.obj# = col$.obj#
6 and obj$.owner# = user$.user#
7 and user$.name = 'USER1'
8 and obj$.name = 'COLA_MARKETS';
ADT_NAME
--------------------------------------------------------------------------------
REL_NAME
------------------------------
"SHAPE"."SDO_GTYPE"
SYS_NC00004$
"SHAPE"."SDO_SRID"
SYS_NC00005$
"SHAPE"."SDO_POINT"."X"
SYS_NC00006$
"SHAPE"."SDO_POINT"."Y"
SYS_NC00007$
"SHAPE"."SDO_POINT"."Z"
SYS_NC00008$
"SHAPE"."SDO_ELEM_INFO"
SYS_NC00009$
"SHAPE"."SDO_ORDINATES"
SYS_NC00010$
7 rows selected.
SQL>
SQL> connect user1/user1@inst2
Connected.
SQL> -- Apply dml handler for rel_cola_market logs. This lcr will only process
SQL> -- the inserts (and lob chunks) and will ignore deletes
SQL> create or replace procedure user1.cola_data_handler
(lcr_anydata IN Sys.AnyData) is
2 lcr sys.lcr$_row_record;
3 tc number;
4 l_dmltype varchar2(1) := NULL;
5 lo_mkt_id number := NULL;
6 ln_mkt_id number := NULL;
7 ln_name varchar2(32) := NULL;
8 ln_shape raw(16) := NULL;
9 ln_shape_gtype number := NULL;
10 ln_shape_srid number := NULL;
11 ln_shape_sdo_point_x number := NULL;
12 ln_shape_sdo_point_y number := NULL;
13 ln_shape_sdo_point_z number := NULL;
14 ln_shape_sdo_elem_info blob;
15 ln_shape_sdo_ordinates blob;
16 tmp_ord_lob_created boolean;
17 tmp_elem_lob_created boolean;
18 ordinates_lob blob;
19 elem_lob blob;
20 BEGIN
21 tc := lcr_anydata.getobject(lcr);
22
23 -- we will only process inserts and ignoring any deletes (Note: we can
24 -- modify our rules at the capture to ensure that the deletes are filtered
25 -- out directly at capture time itself)
26 IF (lcr.get_command_type = 'INSERT') THEN
27 -- get the column values
28 l_dmltype := my_getvarchar(lcr, 'dmltype');
29 ln_mkt_id := my_getnumber(lcr, 'n_mkt_id');
30 ln_name := my_getvarchar(lcr, 'n_name');
31 ln_shape := my_getraw(lcr, 'n_shape');
32 ln_shape_gtype := my_getnumber(lcr, 'n_shape_gtype');
33 ln_shape_srid := my_getnumber(lcr, 'n_shape_srid');
34 ln_shape_sdo_point_x := my_getnumber(lcr, 'n_shape_sdo_point_x');
35 ln_shape_sdo_point_y := my_getnumber(lcr, 'n_shape_sdo_point_y');
36 ln_shape_sdo_point_z := my_getnumber(lcr, 'n_shape_sdo_point_z');
37 tmp_elem_lob_created := my_getblob(lcr, 'n_shape_sdo_elem_info',
38 ln_shape_sdo_elem_info);
39 tmp_ord_lob_created := my_getblob(lcr, 'n_shape_sdo_ordinates',
40 ln_shape_sdo_ordinates);
41 lo_mkt_id := my_getnumber(lcr, 'o_mkt_id');
42
43 -- if the original dml was an insert, then insert a row into cola_markets
44 IF (l_dmltype = 'I') THEN
45 INSERT /*+ relational(cola_markets) */ INTO cola_markets
46 (mkt_id, name, shape,
47 sys_nc00004$, sys_nc00005$, sys_nc00006$, sys_nc00007$,
48 sys_nc00008$, sys_nc00009$, sys_nc00010$)
49 VALUES(ln_mkt_id, ln_name, ln_shape,
50 ln_shape_gtype, ln_shape_srid,
51 ln_shape_sdo_point_x, ln_shape_sdo_point_y, ln_shape_sdo_point_z,
52 SYS_ET_BLOB_TO_IMAGE(ln_shape_sdo_elem_info,
53 'SDO_ELEM_INFO_ARRAY',
54 'USER1'),
55 SYS_ET_BLOB_TO_IMAGE(ln_shape_sdo_ordinates,
56 'SDO_ORDINATE_ARRAY',
57 'USER1'));
58 ELSIF (l_dmltype = 'D') THEN
59 -- if the original dml was a delete, then delete from cola_markets
60 DELETE FROM cola_markets WHERE mkt_id = lo_mkt_id;
61 ELSIF (l_dmltype = 'U') THEN
62 -- if the original dml was an update then update from cola_markets
63 UPDATE /*+ relational(cola_markets) */ cola_markets
64 SET mkt_id = ln_mkt_id,
65 name = ln_name,
66 shape = ln_shape,
67 sys_nc00004$ = ln_shape_gtype,
68 sys_nc00005$ = ln_shape_srid,
69 sys_nc00006$ = ln_shape_sdo_point_x,
70 sys_nc00007$ = ln_shape_sdo_point_y,
71 sys_nc00008$ = ln_shape_sdo_point_z,
72 sys_nc00009$ = SYS_ET_BLOB_TO_IMAGE(ln_shape_sdo_elem_info,
73 'SDO_ELEM_INFO_ARRAY',
74 'USER1'),
75 sys_nc00010$ = SYS_ET_BLOB_TO_IMAGE(ln_shape_sdo_ordinates,
76 'SDO_ORDINATE_ARRAY',
77 'USER1')
78 WHERE mkt_id = lo_mkt_id;
79 END IF;
80
81 -- free any temporary lobs
82 if (tmp_elem_lob_created) then
83 dbms_lob.freetemporary(ln_shape_sdo_elem_info);
84 end if;
85
86 if (tmp_ord_lob_created) then
87 dbms_lob.freetemporary(ln_shape_sdo_ordinates);
88 end if;
89
90 ELSIF (lcr.get_command_type = 'LOB_WRITE') THEN
91 -- Note: this piece of code needs to rewritten and optimized further
92 -- It is here more for illustration purposes
93 -- get key column
94 ln_mkt_id := my_getnumber(lcr, 'mkt_id');
95
96 -- select lob locators
97 SELECT /*+ relational(user1.cola_markets) */
98 SYS_ET_IMAGE_TO_BLOB(sys_nc00009$),
99 SYS_ET_IMAGE_TO_BLOB(sys_nc00010$)
100 INTO elem_lob, ordinates_lob
101 FROM user1.cola_markets
102 WHERE mkt_id = ln_mkt_id;
103
104
105 tmp_elem_lob_created := my_getblob(lcr,'n_shape_sdo_elem_info',
106 ln_shape_sdo_elem_info);
107 if (tmp_elem_lob_created) then
108 dbms_lob.append(elem_lob, ln_shape_sdo_elem_info);
109 dbms_lob.freetemporary(ln_shape_sdo_elem_info);
110 end if;
111
112 tmp_ord_lob_created := my_getblob(lcr, 'n_shape_sdo_ordinates',
113 ln_shape_sdo_ordinates);
114 if (tmp_ord_lob_created) then
115 dbms_lob.append(ordinates_lob, ln_shape_sdo_ordinates);
116 dbms_lob.freetemporary(ln_shape_sdo_ordinates);
117 end if;
118 END IF;
119 END;
120 /
Procedure created.
SQL>
SQL> -- ------------------------------------------------------------
SQL> -- START APPLY AT INST2
SQL> -- ------------------------------------------------------------
SQL> CONNECT stradm/stradm@INST2
Connected.
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_PARAMETER(
3 APPLY_NAME => 'apply_from_inst1',
4 PARAMETER => 'DISABLE_ON_ERROR',
5 VALUE => 'N');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 APPLY_NAME => 'apply_from_inst1');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- register apply handler
SQL> begin
2 dbms_apply_adm.set_dml_handler
3 ( 'USER1.REL_COLA_MARKETS_LOG','TABLE','DEFAULT',FALSE,
4 'USER1.COLA_DATA_HANDLER' );
5 end ;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> CONNECT stradm/stradm@INST1
Connected.
SQL> BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 CAPTURE_NAME => 'capture_user1');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> CONNECT user1/user1@INST1
Connected.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> -- do some dml to the tables
SQL> INSERT INTO cola_markets VALUES(
2 13,
3 'type_zero_element_geom',
4 USER1.SDO_GEOMETRY(
5 2003, -- 2-dimensional polygon
6 NULL,
7 NULL,
8 USER1.SDO_ELEM_INFO_ARRAY(1,0,57, 11,1003,3), -- 1st is type 0 element
9 USER1.SDO_ORDINATE_ARRAY(6,6, 12,6, 9,8, 6,10, 12,10, 6,4, 12,12)
10 )
11 );
1 row created.
SQL>
SQL> INSERT INTO cola_markets VALUES(
2 2,
3 'type_zero_element_geom',
4 USER1.SDO_GEOMETRY(
5 2003, -- 2-dimensional polygon
6 NULL,
7 NULL,
8 USER1.SDO_ELEM_INFO_ARRAY(1,0,57, 11,1003,3), -- 1st is type 0 element
9 USER1.SDO_ORDINATE_ARRAY(6,6, 12,6, 9,8, 6,10, 12,10, 6,4, 12,12)
10 )
11 );
1 row created.
SQL>
SQL>
SQL> INSERT INTO cola_markets VALUES(
2 1,
3 'type_zero_element_geom',
4 USER1.SDO_GEOMETRY(
5 2003, -- 2-dimensional polygon
6 NULL,
7 NULL,
8 USER1.SDO_ELEM_INFO_ARRAY(1,0,57, 11,1003,3), -- 1st is type 0 element
9 USER1.SDO_ORDINATE_ARRAY(6,6, 12,6, 9,8, 6,10, 12,10, 6,4, 12,12)
10 )
11 );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> UPDATE cola_markets set mkt_id = 15 where mkt_id = 1;
1 row updated.
SQL>
SQL>
SQL> DELETE cola_markets where mkt_id = 2;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
SQL> select * from cola_markets;
MKT_ID NAME
---------- --------------------------------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
13 type_zero_element_geom
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 0, 57, 11, 1003, 3), SDO_O
RDINATE_ARRAY(6, 6, 12, 6, 9, 8, 6, 10, 12, 10, 6, 4, 12, 12))
15 type_zero_element_geom
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 0, 57, 11, 1003, 3), SDO_O
RDINATE_ARRAY(6, 6, 12, 6, 9, 8, 6, 10, 12, 10, 6, 4, 12, 12))
2 rows selected.
SQL>
SQL>
SQL> CONNECT SYSTEM/MANAGER@INST1
Connected.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> -- sleep for a while and then verify the changes have been applied successfully
SQL> -- Note: there will be a significant time dml till the first dml is propagated.
SQL> -- This is because the logminer/capture process needs to setup initialize
SQL> -- its internal metadata. After that the capture/propagation/apply will
SQL> -- be quick
SQL>
SQL> exec dbms_lock.sleep(500);
PL/SQL procedure successfully completed.
SQL>
SQL> connect user1/user1@inst2
Connected.
SQL> select * from cola_markets;
MKT_ID NAME
---------- --------------------------------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
13 type_zero_element_geom
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 0, 57, 11, 1003, 3), SDO_O
RDINATE_ARRAY(6, 6, 12, 6, 9, 8, 6, 10, 12, 10, 6, 4, 12, 12))
15 type_zero_element_geom
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 0, 57, 11, 1003,
3), SDO_ORDINATE_ARRAY(6, 6, 12, 6, 9, 8, 6, 10, 12, 10, 6, 4, 12, 12))
2 rows selected.
SQL>
SQL>