oracle从远程数据库用存储过程同步数据

--笔记--查看数据库中存在的DBLINK，以SYSTEM，SCOTT有权限的用户SELECT * FROM DBA_DB_LINKS;SELECT OWNER, OBJECT_NAME  FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK';--创建DBLINK的权限，授权，PUBLIC DATABASE LINK公共的都可以访问，DATABASE LINK当前用户才可以访问GRANT CREATE PUBLIC DATABASE LINK, CREATE DATABASE LINK TO 用户名;--1.在源库创建到目标库的XIAOYU输入所要创建XIAOYU的名称，自定义 CREATE DATABASE LINK XIAOYU--设置连接远程数据库的用户名和密码 CONNECT TO 用户名 IDENTIFIED BY 密码--指定目标数据库的连接方式，可用TNS名称USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.101.10)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME =SERVICENAME)))';--创建存储过程名为GET_INFO--从TABLE_A表同步数据到TABLE_B表CREATE OR REPLACE PROCEDURE GET_INFO IS V_COUNT INT;V_ERROR_MSG NVARCHAR2(2000);BEGIN  SELECT COUNT(*) INTO V_COUNT FROM TABLE_A@XIAOYU;  IF V_COUNT = 0 THEN    INSERT INTO TABLE_B      (A, B, C, D, E, F, G)      SELECT A, B, C, D, E, F, G FROM TABLE_A@XIAOYU;  ELSE    BEGIN      MERGE INTO TABLE_B A      USING (SELECT A, B, C, D, E, F, G FROM TABLE_A@XIAOYU) B      ON (A.A = B.A)      WHEN MATCHED THEN        UPDATE           SET A.A = B.A,               A.B = B.B,               A.C = B.C,               A.D = B.D,               A.E = B.E,               A.F = B.F,               A.G = B.G      WHEN NOT MATCHED THEN        INSERT          (A, B, C, D, E, F, G)        VALUES          (B.A, B.B, B.C, B.D, B.E, B.F, B.G);    END;  END IF;  COMMIT; --提交  INSERT INTO ERROR_LOG VALUES (ID, '数据同步', '', SYSDATE); --添加信息到日志表EXCEPTION  --出现异常  WHEN OTHERS THEN    BEGIN      DBMS_OUTPUT.PUT_LINE('数据同步:' || SQLERRM); --打印错误信息      ROLLBACK; --回滚      V_ERROR_MSG := SQLERRM;      INSERT INTO ERROR_LOG      VALUES        (ID, '数据同步', 'V_ERROR_MSG ' ，SYSDATE);    END;END;