Oracle records the processing package of logs in a stored procedure

Java development process generally use LOG4J to log the program's running logs to the file, in the Oracle stored procedures also need to log, I will work in a log of my own package to share out, in fact, very simple, I hope that we have more comments.

First, table structure

In order to be able to view the relevant logs for operations personnel, the log is best recorded in the table, the table information is as follows:

The serial number is not required, the log level refers to the log4j of the log type, but not all brought over, just a few of the commonly used to take over.

Second, the realization

The implementation is to insert data according to the table structure, here in order to more convenient, define a package, insert the field is 4000 of the length of the interception, in detail as follows:

[SQL]

Create or Replace package body Pkg_log is
/* Stored Procedure logging */
/* Record message */
PROCEDURE INFO (p_proc_name varchar2, title varchar2, content Varchar2) is
Begin
INSERT into T_proc_logs (xh,proc_mc,title,content, Log_type,log_time)
VALUES (seq_t_proc_logs_xh. Nextval, Upper (P_proc_name), substr (title,1,4000), substr (content,1,4000), ' INFO ', sysdate);
COMMIT;
EXCEPTION when OTHERS Then
NULL;
ROLLBACK;
END;
/* Record Test */
PROCEDURE DEBUG (p_proc_name varchar2, title varchar2, content Varchar2) is
Begin
INSERT into T_proc_logs (xh,proc_mc,title,content, Log_type,log_time)
VALUES (seq_t_proc_logs_xh. Nextval, Upper (P_proc_name), substr (title,1,4000), substr (content,1,4000), ' DEBUG ', sysdate);
COMMIT;
EXCEPTION when OTHERS Then
NULL;
ROLLBACK;
END;
/* Record Error */
PROCEDURE ERROR (p_proc_name varchar2, title varchar2, content Varchar2) is
Begin
INSERT into T_proc_logs (xh,proc_mc,title,content, Log_type,log_time)
VALUES (seq_t_proc_logs_xh. Nextval, Upper (P_proc_name), substr (title,1,4000), substr (content,1,4000), ' ERROR ', sysdate);
COMMIT;
EXCEPTION when OTHERS Then
NULL;
ROLLBACK;
END;

End Pkg_log;

Third, use as long as the need to log the call: Pkg_log.info (' Log title ', ' Log content ') can be, and the use of log4j consistent. Related Source: http://download.csdn.net/detail/hereyouare/8380443 four, Expansion and refinement 1. You can make an interface, query the log table, it is more convenient to view the log, and the query whether there is an error log, to determine whether the specified stored procedures run properly. 2. Because the log is recorded in the database, it is necessary to set up a timed task, the debug or info data, deleted through the log time, to ensure that the log table data does not grow excessively.

Oracle records the processing package of logs in a stored procedure