Home » Server Options » Replication » replication with oracle xe (Oracle 10g xe, Windows xp)
replication with oracle xe [message #315628] Tue, 22 April 2008 04:29 Go to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
Hi all,
i work in an application that should make the replication from a publusher table to a remote subscribe table, using snapshot,and trigger, replication data of update works perfectly (update,insert,delete), but when i try to add or dropp a clumn in the publisher table, repplication fail, i know that my method d'ont replicate ddl statment like create or alter table, so i would like the better way to do the replication of the ddl statment without loosing tha data in the subscribe table, i'm working with oracle XE, so couldn't use streams or advanced replication.
thanks for any help.
Re: replication with oracle xe [message #316651 is a reply to message #315628] Sat, 26 April 2008 23:39 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> DESC publisher
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER(1)
 COL2                                               NUMBER
 COL3                                               NUMBER

SCOTT@orcl_11g> SELECT * FROM publisher
  2  /

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3

SCOTT@orcl_11g> DESC subscribe@orcl_11g@loopback
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER(1)
 COL2                                               NUMBER
 COL3                                               NUMBER

SCOTT@orcl_11g> SELECT * FROM subscribe@orcl_11g@loopback
  2  /

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER ALTER ON SCHEMA
  3  DECLARE
  4    n	    number;
  5    v_sql_text   ora_name_list_t;
  6    v_sql	    VARCHAR2 (32767);
  7    v_job	    NUMBER;
  8  BEGIN
  9    IF ora_dict_obj_name = 'PUBLISHER' AND ora_dict_obj_type = 'TABLE' THEN
 10  	 n := ora_sql_txt (v_sql_text);
 11  	 FOR i IN 1 .. v_sql_text.COUNT LOOP
 12  	   v_sql := v_sql || ' ' || v_sql_text(i);
 13  	 END LOOP;
 14  	 v_sql := 'EXECUTE IMMEDIATE '''
 15  	       || LTRIM (RTRIM (REPLACE (UPPER (v_sql), 'PUBLISHER', 'SUBSCRIBE'), CHR(0)), CHR(0))
 16  	       || ''';';
 17  	 DBMS_JOB.SUBMIT@orcl_11g@loopback (v_job, v_sql, SYSDATE);
 18    END IF;
 19  END test_trig;
 20  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> ALTER TABLE publisher MODIFY (col1 NUMBER (5))
  2  /

Table altered.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> ALTER TABLE publisher DROP COLUMN col2
  2  /

Table altered.

SCOTT@orcl_11g> ALTER TABLE publisher ADD (col4 DATE)
  2  /

Table altered.

SCOTT@orcl_11g> EXEC DBMS_LOCK.SLEEP (15)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> DESC publisher
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER(5)
 COL3                                               NUMBER
 COL4                                               DATE

SCOTT@orcl_11g> SELECT * FROM publisher
  2  /

      COL1       COL3 COL4
---------- ---------- ---------
         1          3

SCOTT@orcl_11g> DESC subscribe@orcl_11g@loopback
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER(5)
 COL3                                               NUMBER
 COL4                                               DATE

SCOTT@orcl_11g> SELECT * FROM subscribe@orcl_11g@loopback
  2  /

      COL1       COL3 COL4
---------- ---------- ---------
         1          3

SCOTT@orcl_11g> 


Previous Topic: Materialized view
Next Topic: Alter mview (Query)
Goto Forum:
  


Current Time: Thu Mar 28 07:43:23 CDT 2024