Sunday, July 12, 2015

Oracle Procedure to Write Log into a File

Sometimes you have to write in Oracle a long running time PL/SQL script and you want to log to file what is happening during the execution, in order to check the status, or just for event logging purpose. Here I propose a simple stored procedure for Oracle Database that allows you to do that, using it instead of the classic DBMS_OUTPUT.PUTLINE('') to write on dbms console.


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

(c) Copyright 2020 - MyTroubleshooting.com