Stored Procedure
Here is the stored procedure that do the work. Just copy and execute the code to create it:CREATE OR REPLACE PROCEDURE "WRITELOG" (LOGLEVEL IN VARCHAR2, LOGCONTEXT IN VARCHAR2, LOGMESSAGE IN VARCHAR2) AUTHID CURRENT_USER AS F1 UTL_FILE.FILE_TYPE; PRAGMA AUTONOMOUS_TRANSACTION; LOG_DIR VARCHAR2; LOG_FILENAME VARCHAR2; BEGIN LOG_DIR := 'MYLOG_DIR'; LOG_FILENAME := 'mylog'; F1 := UTL_FILE.FOPEN(LOG_DIR, TO_CHAR(SYSDATE,'YYYY-MM-DD') || '_' || LOG_FILENAME ||'.log','a'); UTL_FILE.PUT_LINE(F1, TO_CHAR(SYSDATE,'DD-MM-YYYY HH:MI:SS AM') || ' - [' || LOGLEVEL || '] ' || LOGCONTEXT || ': ' || LOGMESSAGE); UTL_FILE.FCLOSE(F1); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || TO_CHAR(SQLCODE) || SQLERRM); IF UTL_FILE.IS_OPEN(F1) THEN UTL_FILE.FCLOSE(F1); END IF; END;
You have to specify the ORACLE DIRECTORY NAME (not the filesystem directory!) in which logs will be written, in this example 'MYLOG_DIR' and the base file name, in this case "mylog".
The procedure will create in that directory the log files in the format "YYYYMMDD_filename.log"
If you have not previously created an Oracle Directory you have to create it, i.e.:
CREATE OR REPLACE DIRECTORY MYLOG_DIR AS 'C:\oracle_logs';
Then assure that the current user has the read permissions to that directory.
GRANT READ, WRITE ON MYLOG_DIR TO PUBLIC
to grant full access to public, or if you want to grant access just to a specified user (i.e MYUSER):
GRANT READ, WRITE ON MYLOG_DIR TO MYUSER
To avoid any problem assure also that the physical directory in the filesystem is accessibile for R/W as well, most often you have to verify it in linux system than in windows system (more permissive).
Usage
You can now use this procedure in your own script as in the following example:WRITELOG('INFO', 'MYCONTEXT', 'This is my log text');
So you can specify a log level (i.e. INFO, WARN, ERROR, DEBUG) a context (i.e. the procedure name or something else) and the log text.
Output
The output is a file (or more if the scripts takes 2 or more days) placed in the specified directory, with the specified name and the execution day, i.e.:20150707_mylog.log
The content in the file will be something like:
2015-07-07 16:05:11 - [INFO] MYCONTEXT - This is my log text
No comments:
Post a Comment