Oracle data table Data Synchronization common method

I wrote a data synchronization method, data synchronization between two databases, you can call the stored procedures, add jobs to implement timed synchronization data.

CREATE OR REPLACE PROCEDURE Data_sync_common (tableName in varchar2) is V_sql VARCHAR2 (20000); --???? SQL pk_col_name VARCHAR2 (800); --PRIMARY key SQL Insert_col_name_a VARCHAR2 (20000); --a table field Insert_col_name_b VARCHAR2 (20000); --b table field Update_col_name VARCHAR2 (20000);  --Update Field Dsql VARCHAR2 (20000);  Local_col_in VARCHAR2 (20000);  TYPE Cur_type is REF CURSOR;  Cur_not_contain_col Cur_type; CURSOR CR1 is--select ' ALTER TABLE ' | | table_name| | ' disable constraint ' | |       Constraint_name as Dsql SELECT table_name, constraint_name from user_constraints WHERE constraint_type = ' R ' and table_name = UPPER (tableName); BEGIN SELECT To_char (Wmsys. Wm_concat ("' | | | column_name | |  ") into the local_col_in from user_tab_cols WHERE table_name = UPPER (tableName);          Dsql: = ' SELECT * from [email protected]_dblink1 WHERE table_name= ' ' | | Upper (TableName) | | "And column_name Not in (' | | | local_col_in | |  ‘)‘; Dbms_output.put_line (Dsql); OPEN Cur_not_contain_col for Dsql; SELECT REPLACE (To_char (Wmsys. Wm_concat (' and A. ' | | cu.                                         column_name | | ' =b ' | | Aul  column_name)), ', ', ') into the pk_col_name from User_cons_columns cu, user_constraints Au WHERE cu.constraint_name = au.constraint_name and Au.constraint_type = ' P ' and au.table_name = Upper (Tablena  ME); IF (Pk_col_name is NULL) OR (LENGTH (Pk_col_name) < 6) then SELECT REPLACE (To_char (Wmsys. Wm_concat (' and A. ' | | cu.                                           column_name | | ' =b ' | | Aul column_name)), ', ', ') into the pk_col_name from User_cons_columns cu, User_con  Straints au WHERE cu.constraint_name = au.constraint_name and Au.constraint_type = ' U ' and au.table_name =  Upper (TableName);  END IF; SELECT To_char (Wmsys. Wm_concat (' B. ' | | column_name)) into the insert_col_name_b from user_tab_cols WHERE table_name = UPPER (tAblename); SELECT To_char (Wmsys.  Wm_concat (' A. ' | | column_name)) into the insert_col_name_a from user_tab_cols WHERE table_name = UPPER (tableName); SELECT To_char (Wmsys. Wm_concat (' A. ' | | column_name | |                                 ' =b ' | | column_name) to Update_col_name from user_tab_cols WHERE table_name = UPPER (TableName) and column_name not In (SELECT cu. column_name from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_n  Ame and Au.constraint_type = ' P ' and au.table_name = Upper (TableName)); V_sql: = ' MERGE into ' | | Upper (TableName) | | ' @testjob_dblink1 a USING ' | |           Upper (TableName) | | ' B ' | | ' On (' | | | substr (pk_col_name, 6, Length (pk_col_name)) | |   ‘)‘;  Dbms_output.put_line (Update_col_name); IF (Update_col_name is isn't NULL) and (LENGTH (Update_col_name) > 0) then v_sql: = V_sql | | ' When matched then update set ' | |  Update_col_name; END IF; V_sql: = V_sql | | ' When isn't matched then insert (' | | insert_col_name_a | |           ') VALUES (' | | | Insert_col_name_b | |  ‘)‘;  Dbms_output.put_line (V_sql);  EXECUTE immediate (V_SQL); EXCEPTION when OTHERS then Dbms_output.put_line (SQLERRM); END Data_sync_common;-----------------------------call the stored procedure above create OR REPLACE PROCEDURE data_sync isbegindata_sync_ Common (' Test_sync '); COMMIT; EXCEPTION when OTHERS and then ROLLBACK; END Data_sync;

Oracle data table Data Synchronization common method