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;/