存储过程记录日志、监听及定时器

一.打印日志（此处只提供包体结构，调用方式见上篇）

1.packages

（1）BT

CREATE OR REPLACE PACKAGE BT IS
 
  TYPE ERROR_RT IS RECORD(
    PROGRAM_OWNER ALL_OBJECTS.OWNER%TYPE,
    PROGRAM_NAME  ALL_OBJECTS.OBJECT_NAME%TYPE,
    LINE_NUMBER   PLS_INTEGER);
 
  FUNCTION INFO(BACKTRACE_IN IN VARCHAR2) RETURN ERROR_RT;
 
  PROCEDURE SHOW_INFO(BACKTRACE_IN IN VARCHAR2);
 
END BT;

（2）LOG_PKG

CREATE OR REPLACE PACKAGE LOG_PKG IS
 
  PROCEDURE PUTLINE(CODE_IN IN INTEGER,
                    TEXT_IN IN VARCHAR2,
                    LINE_NO IN INTEGER,
                    P_NAME  IN VARCHAR2);
 
  PROCEDURE SAVE_ERROR_LOG(FORMAT_ERROR_BACKTRACE VARCHAR2,
                           V_SQLERRM              VARCHAR2);
 
END LOG_PKG;

二.package Bodies

（1）BT

CREATE OR REPLACE PACKAGE BODY BT IS
 
  C_NAME_DELIM CONSTANT CHAR(1) := '"';
  C_DOT_DELIM  CONSTANT CHAR(1) := '.';
  C_LINE_DELIM CONSTANT CHAR(4) := 'line';
  C_EOL_DELIM  CONSTANT CHAR(1) := CHR(10);
 
 
  FUNCTION INFO(BACKTRACE_IN IN VARCHAR2) RETURN ERROR_RT IS
 
    L_AT_LOC         PLS_INTEGER;
    L_DOT_LOC        PLS_INTEGER;
    L_NAME_START_LOC PLS_INTEGER;
    L_NAME_END_LOC   PLS_INTEGER;
    L_LINE_LOC       PLS_INTEGER;
    L_EOL_LOC        PLS_INTEGER;
 
    RETVAL ERROR_RT;
 
    PROCEDURE INITIALIZE_VALUES IS
    BEGIN
      L_NAME_START_LOC := INSTR(BACKTRACE_IN, C_NAME_DELIM, 1, 1);
      L_DOT_LOC        := INSTR(BACKTRACE_IN, C_DOT_DELIM);
      L_NAME_END_LOC   := INSTR(BACKTRACE_IN, C_NAME_DELIM, 1, 2);
      L_LINE_LOC       := INSTR(BACKTRACE_IN, C_LINE_DELIM);
      L_EOL_LOC        := INSTR(BACKTRACE_IN, C_EOL_DELIM);
 
      IF L_EOL_LOC = 0 THEN
        L_EOL_LOC := LENGTH(BACKTRACE_IN) + 1;
      END IF;
    END INITIALIZE_VALUES;
  BEGIN
    INITIALIZE_VALUES;
 
    RETVAL.PROGRAM_OWNER := SUBSTR(BACKTRACE_IN,
                                   L_NAME_START_LOC + 1,
                                   L_DOT_LOC - L_NAME_START_LOC - 1);
 
    RETVAL.PROGRAM_NAME := SUBSTR(BACKTRACE_IN,
                                  L_DOT_LOC + 1,
                                  L_NAME_END_LOC - L_DOT_LOC - 1);
 
    RETVAL.LINE_NUMBER := SUBSTR(BACKTRACE_IN,
                                 L_LINE_LOC + 5,
                                 L_EOL_LOC - L_LINE_LOC - 5);
    RETURN RETVAL;
  END INFO;
 
  PROCEDURE SHOW_INFO(BACKTRACE_IN IN VARCHAR2) IS
    L_LINE ERROR_RT;
  BEGIN
    L_LINE := INFO(BACKTRACE_IN);
    DBMS_OUTPUT.PUT_LINE('Program owner = ' || L_LINE.PROGRAM_OWNER);
    DBMS_OUTPUT.PUT_LINE('Program name = ' || L_LINE.PROGRAM_NAME);
    DBMS_OUTPUT.PUT_LINE('Line number = ' || L_LINE.LINE_NUMBER);
  END SHOW_INFO;
END BT;

（2）LOG_PKG

CREATE OR REPLACE PACKAGE BODY LOG_PKG IS
  PROCEDURE PUTLINE(CODE_IN IN INTEGER,
                    TEXT_IN IN VARCHAR2,
                    LINE_NO IN INTEGER,
                    P_NAME  IN VARCHAR2) IS
  BEGIN
    INSERT INTO LOGTAB
      (CODE,
       LINE_NO,
       P_NAME,
       TEXT,
       CREATED_ON,
       CREATED_BY,
       CHANGED_ON,
       CHANGED_BY)
    VALUES
      (CODE_IN, LINE_NO, P_NAME, TEXT_IN, SYSDATE, USER, SYSDATE, USER);
  END;
 
  PROCEDURE SAVE_ERROR_LOG(FORMAT_ERROR_BACKTRACE VARCHAR2,
                           V_SQLERRM              VARCHAR2) IS
    L_LINE BT.ERROR_RT;
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    L_LINE := BT.INFO(FORMAT_ERROR_BACKTRACE);
    PUTLINE(SQLCODE, V_SQLERRM, L_LINE.LINE_NUMBER, L_LINE.PROGRAM_NAME);
  
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLERRM);
      ROLLBACK;
  END;
END LOG_PKG;

三.types

CREATE OR REPLACE TYPE "STR_SPLIT" IS TABLE OF VARCHAR2 (4000);

四.表结构：

（1）LOGTAB

-- Create table
create table LOGTAB
(
  code       INTEGER,
  line_no    INTEGER,
  p_name     VARCHAR2(200),
  text       VARCHAR2(4000),
  created_on DATE,
  created_by VARCHAR2(100),
  changed_on DATE,
  changed_by VARCHAR2(100)
);

（2）TMS_INTERFACE_LISTENER

-- Create table
create table TMS_INTERFACE_LISTENER
(
  id                NUMBER(19) not null,
  discriminator     VARCHAR2(255 CHAR),
  code              VARCHAR2(50 CHAR),
  task_name         VARCHAR2(100 CHAR),
  last_execute_date TIMESTAMP(6),
  interval_value    NUMBER(19),
  status            VARCHAR2(50 CHAR),
  exception_log     CLOB
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table TMS_INTERFACE_LISTENER
  add primary key (ID);
alter table TMS_INTERFACE_LISTENER
  add unique (CODE);
-- Create sequence 
create sequence SEQ_INTERFACELISTENER
minvalue 1
maxvalue 9999999999999999999999999999
start with 1280
increment by 1
cache 20;

五.操作定时器

--查看定时器
SELECT * FROM ALL_JOBS;
 
--删除单独定时器
declare 
  jobcnt integer :=0;
  job_recompile number := 0;
  str_sql varchar2(200);
begin 
 
 dbms_job.remove(61);
 
end;
 
 
 
--删除定时器执行任务
declare 
  jobcnt integer :=0;
  job_recompile number := 0;
  str_sql varchar2(200);
begin 
    for jobs in (select job from all_jobs all_jobs  where schema_user=user)
    loop
      str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';
      begin
        execute immediate str_sql;
      exception
        When Others Then null;
      end;
    end loop; 
end;
 
 
 
--供应商抓取定时器-- 3分钟一次
declare 
  jobcnt integer :=0;
  job_recompile number := 0;
  str_sql varchar2(200);
begin 
    for jobs in (select job from all_jobs where what = 'PR_LSWLDW;' and broken = 'N')
    loop
      str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';
      begin
        execute immediate str_sql;
      exception
        When Others Then null;
      end;
    end loop; 
  --创建任务计划
  sys.dbms_job.submit(job_recompile,'PR_LSWLDW;',sysdate+1/(24*60),'sysdate+3/(24*60)');
  --启动任务计划
  dbms_job.run(job_recompile);
end;