| replication with oracle xe [message #315628] |
Tue, 22 April 2008 04:29  |
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  |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
