PL/SQL logs

1. Create a table


CREATE TABLE LOG_TABLE
(
  ID             NUMBER,
  TIME           DATE,
  LOG_TYPE       VARCHAR2(40 BYTE),
  MESSAGE_SHORT  VARCHAR2(4000 BYTE),
  MESSAGE_LONG   VARCHAR2(4000 BYTE),
  SOURCE_OWNER   VARCHAR2(40 BYTE),
  SOURCE_NAME    VARCHAR2(40 BYTE),
  SOURCE_TYPE    VARCHAR2(40 BYTE),
  SOURCE_LINENO  VARCHAR2(40 BYTE),
  SQL_TEXT       VARCHAR2(4000 BYTE),
  CONSTRAINT PK_LOG_ID PRIMARY KEY(ID)
);



2. Create procedure

CREATE OR REPLACE PROCEDURE WRITE_LOG(LOG_TYPE IN VARCHAR2,MESSAGE_SHORT IN VARCHAR2,MESSAGE_LONG IN VARCHAR2,SQL_TEXT IN VARCHAR2)
AS
/*      Author: ****
        Date: YYYY/MM/DD
        Function: This procedure is to write logs of ACBI;
        How to use: exec  WRITE_LOG(LOG_TYPE,MESSAGE_SHORT,MESSAGE_LONG,SQL_TEXT);
        Parameter: LOG_TYPE is like this 'info' ,'error', 'warning', 'success'
                   MESSAGE_SHORT is simple descript of this log.
                   MESSAGE_LONG is detail descript of this log.
                   SQL_TEXT is optional, it is useful for debug you procedure, if you don't want to record the sql, just leave it null.
       Example 1: before start to execute a important sql, you want to trace whether it start to execute, just call write_log as this:
                         WRITE_LOG('info','start to execute sql',table_name,sql_text);
       Example 2: after execute a important sql, you want to trace whether it execute successful, just call write_log as this:
                         WRITE_LOG('success','finish execute sql',table_name,sql_text);
       Example 3: when exception, just call write_log like this, notice: just copy the follow string to exception, not modify it:
                         WRITE_LOG('ERROR',sqlcode,sqlerrm,null);
  */
    PRAGMA AUTONOMOUS_TRANSACTION;
    LOG_ID NUMBER;
    I_OWNER    VARCHAR2 (30);
    I_NAME     VARCHAR2 (30);
    I_LINENO   NUMBER;
    I_TYPE     VARCHAR2 (30);
BEGIN
        OWA_UTIL.who_called_me (I_OWNER, I_NAME, I_LINENO, I_TYPE);


        SELECT DECODE(MAX(ID),NULL,0,MAX(ID))+1 INTO LOG_ID FROM LOG_TABLE;
        
        INSERT INTO LOG_TABLE
                    (ID,TIME,LOG_TYPE,MESSAGE_SHORT,MESSAGE_LONG,SQL_TEXT,SOURCE_OWNER,SOURCE_NAME,SOURCE_TYPE,SOURCE_LINENO)
                          VALUES
                                (LOG_ID,SYSDATE,LOG_TYPE,MESSAGE_SHORT,MESSAGE_LONG,SQL_TEXT,I_OWNER,I_NAME,I_LINENO,I_TYPE);       
        dbms_output.put_line(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')||' ['||LOG_TYPE||'] '||MESSAGE_SHORT||' '||MESSAGE_LONG);
COMMIT;EXCEPTION WHEN OTHERS THEN dbms_output.put_line(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')||' ['||LOG_TYPE||'] '||MESSAGE_SHORT||' '||MESSAGE_LONG); dbms_output.put_line(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')||' [ERROR] Write Log Error due to ORA-'||sqlcode||': '||sqlerrm);END;/



                
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值