Copy bảng dữ liệu giữa 2 database oracle
DECLARE v_execute BOOLEAN := FALSE; v_host VARCHAR2 (50) := '&&DB_HOST'; v_port VARCHAR2 (50) := '&&DB_PORT'; v_sid VARCHAR2 (50) := '&&DB_SID'; v_user_name VARCHAR2 (50) := '&&DB_USER_NAME'; v_password VARCHAR2 (50) := '&&DB_PASSWORD'; v_connection_string VARCHAR2 (1024) := '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ' || v_host || ')(PORT = ' || v_port || ')))(CONNECT_DATA =(SID = ' || v_sid || ')))'; v_db_link_name VARCHAR2 (1024) := 'TMP_DB_LINK_' || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'); v_statement VARCHAR2 (8192); v_column_list VARCHAR2 (8192); CURSOR c_table IS SELECT table_name FROM user_tables WHERE INSTR (table_name, '$') <= 0 ORDER BY table_name; CURSOR c_column (p_table_name VARCHAR2) IS SELECT column_name FROM user_tab_cols WHERE table_name = p_table_name AND INSTR (column_name, '$') <= 0; BEGIN v_statement := 'CREATE DATABASE LINK ' || v_db_link_name || ' CONNECT TO ' || v_user_name || ' IDENTIFIED BY ' || v_password || ' USING ''' || v_connection_string || ''''; DBMS_OUTPUT.put_line( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Start executing statement:' || CHR (10) || v_statement || ';'); IF v_execute THEN EXECUTE IMMEDIATE v_statement; END IF; DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Completed'); FOR r_table IN c_table LOOP v_column_list := ''; FOR r_column IN c_column (r_table.table_name) LOOP v_column_list := v_column_list || ',' || r_column.column_name; END LOOP; v_column_list := SUBSTR (v_column_list, 2); BEGIN v_statement := 'insert into ' || r_table.table_name || '(' || v_column_list || ')' || chr(10) || 'select ' || v_column_list || ' from ' || r_table.table_name || '@' || v_db_link_name; DBMS_OUTPUT.put_line( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Start executing statement:' || CHR (10) || v_statement || ';'); IF v_execute THEN EXECUTE IMMEDIATE v_statement; END IF; DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Completed'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Error occurred: ' || SQLERRM); END; END LOOP; v_statement := 'DROP DATABASE LINK ' || v_db_link_name; DBMS_OUTPUT.put_line( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Start executing statement:' || CHR (10) || v_statement || ';'); IF v_execute THEN EXECUTE IMMEDIATE v_statement; END IF; DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Completed'); END;