ORACLE 创建job 定时 同步 某张表

--需要授予ALANLEE用户创建数据库链接权限
grant create public database link to test;
--需要授予ALANLEE用户删除数据库链接权限
grant drop public database link to test; 


--创建dblink
create public database link crvbi

connect to CRV_JVBI_APD identified by CRV_JVBI_APD
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = UATBISDM)))';









--日志表
create table job_log
  (
    JOB_NAME   varchar2(10),
    MERGED_TOTAL number,
    INSERTED number,
    updated number,
    operation_date date
    ) ;


CREATE OR REPLACE PACKAGE merge_count AS
  FUNCTION merge_counter RETURN PLS_INTEGER;
  FUNCTION get_merge_insert_count RETURN PLS_INTEGER;
  PROCEDURE reset_counters;
END merge_count;
/




CREATE OR REPLACE PACKAGE BODY merge_count AS
  g_insert_counter PLS_INTEGER NOT NULL := 0;


  FUNCTION merge_counter RETURN PLS_INTEGER IS
  BEGIN
        g_insert_counter := g