CREATE OR REPLACE PACKAGE logFileMgr IS
   /* *****************************************************************************
   * Description
   * ============
   *  This package enables logging in UNIX files system
   *
   *   File:          pkg_akitools_logfilemgr_ph.sql
   *   Modified at:   $Date: 2020-01-01 08:00:00 +0200 (Mi, 01 Jan 2020) $
   *   Checked in by: $Author: AKielhorn $
   *   Revision:      $Revision: 100 $
   *   Author:        Andy Kielhorn
   *   Company:       AkiCom
   *****************************************************************************
   */
   
   /* ====================================================================================
                                         Constants
     =====================================================================================

      ************************************************************************************
                                 L O G G I N G L E V E L S                               
       The logging system introduces different levels (logging levels) of the protocol 
       scope. These are based on the logging levels of the operating system Linux and 
       are defined in the following.
       It applies that all messages will be written to logfile that correspond to the 
       set level and the one below it. 
       For example you set Level 4, so all messages from level 1 to level 4 will be 
       writtento logfile                   
      ************************************************************************************

      Level 1  - "alert"

       With this logging level, a module only outputs messages that are acted upon 
       immediately otherwise the system will suffer a production standstill.
       This message must be forwarded to the person responsible for the system 
       immediately. 
   */
   c_alert CONSTANT PLS_INTEGER := 1;
   
   /* Level 2 - "critical"

      Messages of this logging level indicate a critical status.
      Here, too, the person responsible for the system should be informed.
   */
   c_critical CONSTANT PLS_INTEGER := 2;
   
   /* Level 3 - "error"

      Messages with this logging level log errors in a module.
   */
   c_error CONSTANT PLS_INTEGER := 3;
   
   /* Level 4 - "warning"

     The messages classified by the module as warnings are logged here.
   */
   c_warning CONSTANT PLS_INTEGER := 4;
   
   /* Level 5 - "notice"

      In this level, normal but important information of the modules are logged
   */
   c_notice CONSTANT PLS_INTEGER := 5;
   
   /* Level 6 - "information"

      This level denotes simple information of no major importance, which
      can also be ignored.
   */
   c_info CONSTANT PLS_INTEGER := 6;
   
   /* Level 7 - "debug"

      Debug messages are the least relevant messages and are the most important
      for checking freshly written software for possible errors or for tracking down
      exploited by errors in existing software.
   */
   c_debug CONSTANT PLS_INTEGER := 7;
   
   /* Options for the left site of logging message */
   /* only module name */
   logFormatName    CONSTANT VARCHAR2(10) := 'NAME';
   /* only day and night */
   logFormatTime    CONSTANT VARCHAR2(10) := 'TIME';
   /* only time */
   logFormatHour    CONSTANT VARCHAR2(10) := 'HOUR';
   /* only day */
   logFormatDay     CONSTANT VARCHAR2(10) := 'DAY';
   /* all */
   logFormatAll     CONSTANT VARCHAR2(10) := 'ALL';
   /* only name and time -> short*/
   logFormatShort CONSTANT VARCHAR2(10) := 'SHORT';
   
   /* Errors that can occured during file operations */
   logInvalidPath       CONSTANT    NUMBER := -1;
   logInvalidMode       CONSTANT    NUMBER := -2;
   logInvalidFileHandle CONSTANT    NUMBER := -3;
   logInvalidOperation  CONSTANT    NUMBER := -4;
   logReadError         CONSTANT    NUMBER := -5;
   logWriteError        CONSTANT    NUMBER := -6;
   logInternalError     CONSTANT    NUMBER := -7;
   logNoCFGFile         CONSTANT    NUMBER := -8;
   logLogConfigFound    CONSTANT    NUMBER := -9;
   logWrongOpenMode     CONSTANT    NUMBER := -10;
   logNoDataFound       CONSTANT    NUMBER := -100;
   
   
  /* ******************************************************************************************
     * Function:    logInit
     * Description: initialisation of logging,
     *              read logging configuration
     *
     * Parameter:   i_moduleName    - Name of module logging is initiate for
     *              i_logCfgPath    - Path of Logging configuration file
     *              i_logCfgFile    - Logging configuration file
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
   FUNCTION logInit( i_moduleName  IN VARCHAR2,
                     i_logCfgPath  IN VARCHAR2 DEFAULT '/tmp',
                     i_logCfgFile  IN VARCHAR2 DEFAULT 'logging.cfg') RETURN NUMBER;

    /* ********************************************************************************************************
       * Procedure:      writeLog
       * 
       * Description:    procedure logs all given information in table message_log
       *
       * Parameters:     i_v_moduleName        - name of calling module
       *                 i_n_moduleCodeLine    - line number in code of calling module
       *                                         for messages with given parameters
       *                 i_n_loggingLevel      - logging level of message
       *                 i_v_messageText       - free style message
       *                 i_b_setAppInfo        - TRUE = set application info (DBMS_APPLICATION_INFO)
       ********************************************************************************************************
    */
   PROCEDURE writeLog (i_v_moduleName       IN VARCHAR2,
                       i_n_moduleCodeLine   IN NUMBER,
                       i_n_loggingLevel     IN PLS_INTEGER,
                       i_v_messageText      IN VARCHAR2,
                       i_b_setAppInfo       IN BOOLEAN DEFAULT FALSE);
                       
   /* ********************************************************************************************************
      * Procedure:    writeLog
      * Description:  Procedure logs all given information in table message_log
      *
      * Parameters:   i_n_moduleName        -  name of calling module
      *               i_n_moduleCodeLine    -  line number in code of calling module
      *                                        for messages with given parameters
      *               i_n_loggingLevel      -  logging level of message
      *               i_v_messageText       -  free style message
      *               i_n_dbErrorID         -  Error-ID of DB
      *               i_v_dbErrorText       -  error message
      ********************************************************************************************************
    */

   PROCEDURE writeLog (i_v_moduleName       IN VARCHAR2,
                       i_n_moduleCodeLine   IN NUMBER,
                       i_n_loggingLevel     IN PLS_INTEGER,
                       i_v_messageText      IN VARCHAR2,
                       i_n_dbErrorID        IN NUMBER,
                       i_v_dbErrorText      IN VARCHAR2);
                       
    /* ********************************************************************************************************
       * Procedure:    raiseError
       * Description:  Procedure logs all given information in table message_log and raise DB error
       *
       * Parameters:   i_n_moduleName        -  name of calling module
       *               i_n_moduleCodeLine    -  line number in code of calling module
       *                                        for messages with given parameters
       *               i_n_loggingLevel      -  logging level of message
       *               i_v_messageText       -  free style message
       *               i_n_dbErrorID         -  Error-ID of database
       *               i_v_dbErrorText       -  error message
       ********************************************************************************************************
    */
   PROCEDURE raiseError (i_v_moduleName       IN VARCHAR2,
                         i_n_moduleCodeLine   IN NUMBER,
                         i_n_loggingLevel     IN PLS_INTEGER,
                         i_v_messageText      IN VARCHAR2,
                         i_n_dbErrorID        IN NUMBER,
                         i_v_dbErrorText      IN VARCHAR2);                                           

END LogFileMgr;
/

CREATE OR REPLACE PACKAGE BODY logFileMgr IS

/* *****************************************************************************
   * Description
   * ============
   *  This package enables logging in UNIX files system
   *
   *   File:          pkg_akitools_logfilemgr_pb.sql
   *   Modified at:   $Date: 2020-01-01 08:00:00 +0200 (Mi, 01 Jan 2020) $
   *   Checked in by: $Author: AKielhorn $
   *   Revision:      $Revision: 100 $
   *   Author:        Andy Kielhorn
   *   Company:       AkiCom
   *****************************************************************************
   */
   
   /*  
    ****************************************************************************
    ** global  Constants                                                      **
    **************************************************************************** 
   */   

   cfgDirectoryName  CONSTANT VARCHAR2(30) := 'DIR_LOGGING_CONFIG';
   logDirectoryName  CONSTANT VARCHAR2(30) := 'DIR_LOGGING';
   
   fileModeRead      CONSTANT VARCHAR2(1)  := 'R';
   fileModeWrite     CONSTANT VARCHAR2(1)  := 'W';
   fileModeAppend    CONSTANT VARCHAR2(1)  := 'A';  
   
   e_wrongLoggingLevel EXCEPTION;
   
   /*  
    ****************************************************************************
    ** globale Types                                                          **
    **************************************************************************** 
   */
   
   TYPE t_logCFG IS RECORD( moduleName   VARCHAR2(32),
                            logLevel     NUMBER,
                            logFormat    VARCHAR2(10),
                            logHandle    utl_file.file_type,
                            logFileName  VARCHAR2(128),
                            logFileDir   VARCHAR2(128),
                            oracleDir    VARCHAR2(128));
   
   /*  
    ****************************************************************************
    ** global Variables                                                       **
    **************************************************************************** 
   */
   loggingEnabled   BOOLEAN := TRUE; 
   logCFG           t_logCFG;
   
   
  /* ******************************************************************************************
     * Function:    fileOpen
     * Description: Open given File
     *
     * Parameter:   i_fhandle    - File handle
     *              i_closeAll   - TRUE = close all open handle
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
  FUNCTION fileOpen( i_oracleDirectoryName  IN VARCHAR2,
                      i_fileName            IN VARCHAR2,
                      i_fileOpenMode        IN VARCHAR2,
                      i_fhandle             IN OUT utl_file.file_type) RETURN NUMBER IS
                       
    retCode NUMBER := 0;
   
    e_wrongFileMode  EXCEPTION;

  BEGIN
    IF UPPER(i_fileOpenMode)  NOT IN (fileModeRead, fileModeWrite, fileModeAppend ) THEN
       RAISE e_wrongFileMode;
    END IF;
    -- Is file open?
    IF utl_file.is_open(i_fhandle) = FALSE THEN
      i_fhandle := utl_file.fopen(i_oracleDirectoryName, i_fileName, i_fileOpenMode);
    END IF;
    RETURN retCode;
  EXCEPTION -- if open fails ....
     WHEN utl_file.invalid_path THEN
        RETURN logInvalidPath;
     WHEN utl_file.invalid_mode THEN
        RETURN logInvalidMode;
     WHEN utl_file.invalid_filehandle THEN
        RETURN logInvalidFileHandle;
     WHEN utl_file.invalid_operation THEN
        RETURN logInvalidOperation;
     WHEN utl_file.read_error THEN
        RETURN logReadError;
     WHEN utl_file.write_error THEN
        RETURN logWriteError;
     WHEN utl_file.internal_error THEN
        RETURN logInternalError;
      WHEN no_data_found THEN
         RETURN logNoDataFound;
      WHEN value_error THEN
         RETURN logInternalError;
      WHEN e_wrongFileMode THEN
         RETURN logWrongOpenMode;
   END fileOpen;
   
  /* ******************************************************************************************
     * Function:    fileClose
     * Description: Close given handle
     *
     * Parameter:   i_fhandle    - File handle
     *              i_closeAll   - TRUE = close all open handle
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
  FUNCTION fileClose(i_fhandle   IN OUT utl_file.file_type,
                     i_closeAll IN BOOLEAN DEFAULT FALSE) RETURN NUMBER IS
                       
      retCode NUMBER := 0;
      
  BEGIN
      -- cloase all Files
      IF i_closeAll = TRUE THEN
         utl_file.fclose_all;
      ELSIF utl_file.is_open(i_fhandle) THEN -- if file is open -> close it
         utl_file.fclose(i_fhandle);
      END IF;
      RETURN retCode;
  EXCEPTION -- if close fails ....
     WHEN utl_file.invalid_path THEN
        RETURN logInvalidPath;
     WHEN utl_file.invalid_mode THEN
        RETURN logInvalidMode;
     WHEN utl_file.invalid_filehandle THEN
        RETURN logInvalidFileHandle;
     WHEN utl_file.invalid_operation THEN
        RETURN logInvalidOperation;
     WHEN utl_file.read_error THEN
        RETURN logReadError;
     WHEN utl_file.write_error THEN
        RETURN logWriteError;
     WHEN utl_file.internal_error THEN
        RETURN logInternalError;
      WHEN no_data_found THEN
         RETURN logNoDataFound;
      WHEN value_error THEN
         RETURN logInternalError;
   END fileClose;
   
  /* ******************************************************************************************
     * Function:    fileLineRead
     * Description: Read line in given handle
     *
     * Parameter:   i_fhandle    - File handle
     *              io_line      - Line
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
   FUNCTION fileLineRead (i_fhandle  IN utl_file.file_type,
                          io_line    IN OUT VARCHAR2) RETURN NUMBER IS

     retCode NUMBER := '0';
 
   BEGIN
      -- File open ?
      IF utl_file.is_open(i_fhandle) THEN
         utl_file.get_line(i_fhandle, io_line);
      END IF;
      RETURN(retCode);
  EXCEPTION
     WHEN utl_file.invalid_path THEN
        RETURN logInvalidPath;
     WHEN utl_file.invalid_mode THEN
        RETURN logInvalidMode;
     WHEN utl_file.invalid_filehandle THEN
        RETURN logInvalidFileHandle;
     WHEN utl_file.invalid_operation THEN
        RETURN logInvalidOperation;
     WHEN utl_file.read_error THEN
        RETURN logReadError;
     WHEN utl_file.write_error THEN
        RETURN logWriteError;
     WHEN utl_file.internal_error THEN
        RETURN logInternalError;
      WHEN no_data_found THEN
         RETURN logNoDataFound;
      WHEN value_error THEN
         RETURN logInternalError;
   END FileLineRead;
   
  /* ******************************************************************************************
     * Function:    fileWrite
     * Description: Write a given message in given handle
     *
     * Parameter:   i_fhandle    - File handle
     *              i_message    - Message
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
   FUNCTION fileWrite(i_fhandle  IN utl_file.file_type,
                      i_message  IN VARCHAR2) RETURN NUMBER IS

     retCode NUMBER := '0';
 
   BEGIN
     -- Is file open?
     IF utl_file.is_open(i_fhandle) THEN
        utl_file.put_line(i_fhandle, i_message, TRUE);
     END IF;
     RETURN retCode;
  EXCEPTION
     WHEN utl_file.invalid_path THEN
        RETURN logInvalidPath;
     WHEN utl_file.invalid_mode THEN
        RETURN logInvalidMode;
     WHEN utl_file.invalid_filehandle THEN
        RETURN logInvalidFileHandle;
     WHEN utl_file.invalid_operation THEN
        RETURN logInvalidOperation;
     WHEN utl_file.read_error THEN
        RETURN logReadError;
     WHEN utl_file.write_error THEN
        RETURN logWriteError;
     WHEN utl_file.internal_error THEN
        RETURN logInternalError;
   END fileWrite;
   
  /* ******************************************************************************************
     * Procedure:   createDirectory
     * Description: creating Oracle directories
     *
     * Parameter:   i_Path                    - Path for ORACLE Directory
     *              io_OracleDirectoryName    - Name of ORACLE Directory
     ******************************************************************************************
   */  
   PROCEDURE createDirectory(i_Path                 IN VARCHAR2,
                             io_OracleDirectoryName IN OUT VARCHAR2) IS
    
   oracleDirName DBA_DIRECTORIES.DIRECTORY_NAME%TYPE;
   foundDir      NUMBER := 1;
   versionDir    NUMBER := 0;
   
   BEGIN
     BEGIN
       SELECT DIRECTORY_NAME
         INTO oracleDirName
         FROM DBA_DIRECTORIES
        WHERE DIRECTORY_PATH = i_Path;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
          oracleDirName := 'NONE';
     END;
     -- if OD is not yet defined
     IF io_OracleDirectoryName <> oracleDirName THEN
       IF  oracleDirName = 'NONE' THEN
         LOOP
            SELECT COUNT(1)
              INTO foundDir
             FROM DBA_DIRECTORIES
            WHERE DIRECTORY_NAME = UPPER(io_OracleDirectoryName);  
            EXIT WHEN  foundDir = 0;
            versionDir := versionDir + 1;
            io_OracleDirectoryName := io_OracleDirectoryName || TO_CHAR(versionDir);
         END LOOP;
         EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || io_OracleDirectoryName || ' AS ''' || i_Path || '''';
      ELSE
        -- Path is defined in a different ORACLE directory     
        io_OracleDirectoryName := oracleDirName;
      END IF;
    END IF; 
    EXCEPTION
      WHEN OTHERS THEN
         raise;
    END createDirectory;
    
  /* ******************************************************************************************
     * Function:    readLogCfg
     * Description: Read logging configuration for a given module in given config file
     *
     * Parameter:   i_moduleName    - Name of module logging is initiate for
     *              i_logCfgDir     - Oracle Directory for configuration file
     *              i_logCfgFile    - Logging configuration file
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
   FUNCTION readLogCfg( i_moduleName  IN VARCHAR2,
                        i_logCfgDir   IN VARCHAR2,
                        i_logCfgFile  IN VARCHAR2 DEFAULT 'logging.cfg') RETURN NUMBER
   IS
      strPos NUMBER;
      fhandle      utl_file.file_type;
      retCode      NUMBER := 0;
      lineText     VARCHAR2(255);
      tmpLine      VARCHAR2(255);
      tmpLineOS    VARCHAR2(255);
   BEGIN
     dbms_output.enable(200000);
     IF utl_file.is_open(fhandle) = FALSE THEN
       BEGIN
         fhandle := utl_file.fopen(i_logCfgDir, i_logCfgFile, 'R');
       EXCEPTION
       WHEN OTHERS THEN
         loggingEnabled := FALSE;
         dbms_output.put_line('LogFileMgr-00001  Cannot read ' || i_logCfgFile || ' - Logging is disabled ');
         RETURN logNoCFGFile;
       END;
     END IF;
     -- Set logging config value
     logCFG.moduleName := i_moduleName;
      
     -- Read Line of config file and set logging values
     WHILE retCode <> logNoDataFound
     LOOP
       lineText := NULL;
       retCode := fileLineRead (i_fhandle  => fhandle,
                                io_line  => lineText); 
       tmpLine := LTRIM(RTRIM(lineText));
       lineText := LTRIM(RTRIM(REPLACE(UPPER(lineText), ' ')));
       -- Find Parameter beginning with given module name  
       IF INSTR(lineText, UPPER(i_moduleName)) > 0 THEN
          -- Parameter Logginglevel _LOGLEVEL
          IF INSTR(lineText, '_LOGLEVEL') > 0 THEN
             logCFG.logLevel := TO_NUMBER(SUBSTR(lineText, INSTR(lineText, '=') + 1, LENGTH(lineText)));
          END IF;             
          -- Parameter Logfile _LOGFILE
          IF INSTR(lineText, '_LOGFILE') > 0 THEN
            tmpLineOS := SUBSTR(tmpLine, INSTR(tmpLine, '=') + 1, LENGTH(tmpLine));
            -- UNIX
            IF INSTR(tmpLineOS, '/') > 0 THEN
              logCFG.logFileName := SUBSTR(tmpLineOS, INSTR(tmpLineOS, '/', -1) + 1, 255);
              logCFG.logFileDir   := SUBSTR(tmpLineOS, 1, (LENGTH(tmpLineOS) - LENGTH(logCFG.logFileName)) - 1);
            ELSE  -- Windows
              -- BUG in Windows '' must be masked
              logCFG.logFileName := SUBSTR(tmpLineOS, INSTR(tmpLineOS, '\', -1) + 1, 255);
              logCFG.logFileDir   := SUBSTR(tmpLineOS, 1, (LENGTH(tmpLineOS) - LENGTH(logCFG.logFileName)) - 1);
            END IF;
          END IF; 
          -- Parameter Format _LOGFORMAT
          IF INSTR(lineText, '_LOGFORMAT') > 0 THEN
            logCFG.logFormat := SUBSTR(lineText, INSTR(lineText, '=') + 1, LENGTH(lineText));
          END IF; /* Format */
       END IF;
     END LOOP;
     
     -- Close Config file 
     utl_file.fclose(fhandle);
     IF retCode = logNoDataFound THEN
       retCode := 0;
     END IF;
         
     IF logCFG.logLevel IS NULL THEN
       RETURN logLogConfigFound;
     END IF;
     
     IF logCFG.logLevel < c_alert THEN
       logCFG.logLevel := c_alert;
     END IF;
     
      IF logCFG.logLevel > c_debug THEN
       logCFG.logLevel := c_debug;
     END IF;    
     RETURN(retCode);
   EXCEPTION
     WHEN utl_file.invalid_path THEN
        RETURN logInvalidPath;
     WHEN utl_file.invalid_mode THEN
        RETURN logInvalidMode;
     WHEN utl_file.invalid_filehandle THEN
        RETURN logInvalidFileHandle;
     WHEN utl_file.invalid_operation THEN
        RETURN logInvalidOperation;
     WHEN utl_file.read_error THEN
        RETURN logReadError;
     WHEN utl_file.write_error THEN
        RETURN logWriteError;
     WHEN utl_file.internal_error THEN
        RETURN logInternalError;
   END readLogCfg;
   
  /* ******************************************************************************************
     * Function:    logInit
     * Description: initialisation of logging,
     *              read logging configuration
     *
     * Parameter:   i_moduleName    - Name of module logging is initiate for
     *              i_logCfgPath    - Path of Logging configuration file
     *              i_logCfgFile    - Logging configuration file
     *
     * Returnvalue: 0 = No errors, else Error 
     ******************************************************************************************
   */
   FUNCTION logInit( i_moduleName  IN VARCHAR2,
                     i_logCfgPath  IN VARCHAR2 DEFAULT '/tmp',
                     i_logCfgFile  IN VARCHAR2 DEFAULT 'logging.cfg') RETURN NUMBER
   IS
   
     strPos NUMBER;
     fhandle      utl_file.file_type;
     retCode      NUMBER := 0;
     oraDirName   DBA_DIRECTORIES.DIRECTORY_NAME%TYPE;
     
   BEGIN
      -- reading config file
      oraDirName := cfgDirectoryName;
      createDirectory(i_Path                 => i_logCfgPath,
                      io_OracleDirectoryName => oraDirName);
      retCode := readLogCfg(i_moduleName  => i_moduleName,
                            i_logCfgDir   => oraDirName,
                            i_logCfgFile  => i_logCfgFile);
      IF retCode <> 0 THEN
         loggingEnabled := FALSE;
         RETURN retCode;
      END IF;   
     
      -- intialize Log file
      oraDirName := logDirectoryName;   
      createDirectory(i_Path                 => logCFG.logFileDir,
                      io_OracleDirectoryName => oraDirName);
                
      logCFG.oracleDir := oraDirName;               
      retCode := fileOpen( i_oracleDirectoryName  => oraDirName,
                           i_fileName             => logCFG.logFileName,
                           i_fileOpenMode         => fileModeAppend,
                           i_fhandle              => logCFG.logHandle);
      IF retCode <> 0 THEN
         loggingEnabled := FALSE;
         RETURN retCode;
      END IF;
      loggingEnabled := TRUE;
      RETURN retCode;
  END logInit;
  
  /* ******************************************************************************************
     * Function:    correctLoggingLevel
     * Description: function checkt correct logging level
     * Return       TRUE = Logginglevel correct/ FALSE = otherwise
     ******************************************************************************************
   */

   FUNCTION correctLoggingLevel (i_n_loggingLevel IN PLS_INTEGER)
      RETURN BOOLEAN
   IS
      g_n_minLogLevel   PLS_INTEGER := c_alert;
      g_n_maxLogLevel   PLS_INTEGER := c_debug;
   BEGIN
      IF     (i_n_loggingLevel IS NOT NULL)
         AND (i_n_loggingLevel BETWEEN g_n_minLogLevel AND g_n_maxLogLevel)
      THEN
         RETURN (TRUE);
      END IF;

      RETURN (FALSE);
   END correctLoggingLevel;
   
    /* ********************************************************************************************************
       * Procedure:      getMessageEntry
       * 
       * Description:    get left side entry of message
       *
       * Parameters:     i_v_moduleName        - name of calling module
       *                 i_n_moduleCodeLine    - line number in code of calling module
       *                                         for messages with given parameters
       *                 i_n_loggingLevel      - logging level of message
       *                 i_v_loggingFormat     - logging format
       ********************************************************************************************************
    */
   FUNCTION getMessageEntry (i_v_moduleName     IN  VARCHAR2,
                             i_n_moduleCodeLine IN  NUMBER,
                             i_n_loggingLevel   IN  PLS_INTEGER,
                             i_v_loggingFormat  IN  VARCHAR2) RETURN VARCHAR2 IS

     lineEntry  VARCHAR2(100) := '';
     logTime DATE;
     strPos  NUMBER;
     
   BEGIN
     logTime := SYSDATE;
     strPos := 1;
     IF i_v_moduleName IS NULL THEN
       lineEntry := logCFG.moduleName;
     ELSE
       lineEntry := i_v_moduleName;
     END IF;
     
     IF i_n_moduleCodeLine IS NOT NULL THEN
       lineEntry := lineEntry ||' ('||i_n_moduleCodeLine||')';
     END IF;
     IF i_n_loggingLevel IS NOT NULL THEN
        lineEntry := lineEntry ||' <'||i_n_loggingLevel||'>';
     END IF;
     lineEntry := lineEntry ||': ';
     IF i_v_loggingFormat = 'NAME' THEN
        NULL;
     ELSIF i_v_loggingFormat = 'TIME' THEN
       lineEntry := TO_CHAR(logTime, 'DD.MM.YYYY HH24:MI:SS') || ' ' || lineEntry;
     ELSIF i_v_loggingFormat = 'HOUR' THEN
       lineEntry := TO_CHAR(logTime, 'HH24:MI:SS') || ' ' || lineEntry;
     ELSIF i_v_loggingFormat = 'DAY' THEN
       lineEntry := TO_CHAR(logTime, 'DD.MM.YYYY') || ' ' || lineEntry;
     ELSIF i_v_loggingFormat = 'ALL' THEN
       lineEntry := TO_CHAR(logTime, 'DD.MM.YYYY HH24:MI:SS') || ' ' || lineEntry;
     ELSIF i_v_loggingFormat = 'SHORT' THEN
       lineEntry := TO_CHAR(logTime, 'HH24:MI:SS') || ' ' || lineEntry;
     END IF;
     RETURN lineEntry;
   END getMessageEntry;   
   
 /* ********************************************************************************************************
    * Procedure:     doWrite
    * Description:   Procedure logs all given information in logfile
    *
    * Parameter/parameters:   i_n_loggingLevel      - logging level of message
    *                         i_v_messageText       - free style message
    *                         i_n_moduleName        - name of calling module
    *                         i_n_moduleCodeLine    - line number in code of calling module
    *                                                 for messages with given parameters
    *                         i_n_dbErrorID         - Error-ID of database
    *                         i_v_dbErrorText       - error message
    *                                                                                   
    ********************************************************************************************************
    */

   PROCEDURE doWrite (i_n_loggingLevel     IN PLS_INTEGER,
                      i_v_messageText      IN VARCHAR2,
                      i_v_moduleName       IN VARCHAR2,
                      i_n_moduleCodeLine   IN NUMBER,
                      i_n_dbErrorID        IN NUMBER DEFAULT NULL,
                      i_v_dbErrorText      IN VARCHAR2 DEFAULT NULL)
   IS
     messageEntry  VARCHAR2(300);
     messageLine   VARCHAR2(1000);
     retCode       NUMBER := 0;
     retCodeTmp    NUMBER;
     
   BEGIN 
   
   IF loggingEnabled THEN
     messageEntry := getMessageEntry (i_v_moduleName     => i_v_moduleName,
                                      i_n_moduleCodeLine => i_n_moduleCodeLine,
                                      i_n_loggingLevel   => i_n_loggingLevel,
                                      i_v_loggingFormat  => logCFG.logFormat);
     messageLine := messageEntry || i_v_messageText;
     IF NOT utl_file.is_open(logCFG.logHandle) THEN
       -- open file
       retCode := fileOpen( i_oracleDirectoryName => logCFG.oracleDir,
                            i_fileName            => logCFG.logFileName,
                            i_fileOpenMode        => fileModeAppend,
                            i_fhandle             => logCFG.logHandle);   
       IF retCode <> 0 THEN
         raise_application_error(-20015, 'LogFileMgr: Error '||retCode||' while opening file '||logCFG.logFileName);
       END IF;
     END IF;
     -- write
     retCode := fileWrite(i_fhandle  => logCFG.logHandle,
                          i_message  => messageLine);
     IF retCode = 0 AND i_n_dbErrorID IS NOT NULL THEN
        messageLine := messageEntry || '! Error '|| i_n_dbErrorID ||': '||i_v_dbErrorText;
        retCode := fileWrite(i_fhandle  => logCFG.logHandle,
                             i_message  => messageLine); 
     END IF; 
     IF retCode <> 0 THEN
       -- in case of error close all open files
       retCodeTmp :=  fileClose(i_fhandle   => logCFG.logHandle,
                                i_closeAll  => TRUE);
       IF retCodeTmp <> 0 THEN
         raise_application_error(-20014, 'LogFileMgr: Error '||retCodeTmp||' while closing to file '||logCFG.logFileName);
       END IF;
       IF retCode <> 0 THEN
           raise_application_error(-20013, 'LogFileMgr: Error '||retCode||' while writing file '||logCFG.logFileName);
       END IF;
     END IF;
     -- close
     retCode :=  fileClose(i_fhandle   => logCFG.logHandle,
                           i_closeAll  => FALSE);
     IF retCode <> 0 THEN
       raise_application_error(-20014, 'LogFileMgr: Error '||retCode||' while closing to file '||logCFG.logFileName);
     END IF;
   END IF; /* loggingEnabled = TRUE */
   
   EXCEPTION -- if io fails ....
     WHEN utl_file.invalid_path THEN
        raise_application_error(-20001, 'LogFileMgr: Invalid path while managing file '||logCFG.logFileName);
     WHEN utl_file.invalid_mode THEN
        raise_application_error(-20002, 'LogFileMgr: Invalid mode while managing file '||logCFG.logFileName);
     WHEN utl_file.invalid_filehandle THEN
        raise_application_error(-20003, 'LogFileMgr: Invalid file handle while managing file '||logCFG.logFileName);
     WHEN utl_file.invalid_operation THEN
        raise_application_error(-20004, 'LogFileMgr: Invalid operation while managing file '||logCFG.logFileName);
     WHEN utl_file.read_error THEN
        raise_application_error(-20005, 'LogFileMgr: Read error while managing file '||logCFG.logFileName);
     WHEN utl_file.write_error THEN
        raise_application_error(-20006, 'LogFileMgr: Write error while managing file '||logCFG.logFileName);
     WHEN utl_file.internal_error THEN
        raise_application_error(-20007, 'LogFileMgr: Internal error while managing file '||logCFG.logFileName);
   END doWrite;
      
    /* ********************************************************************************************************
       * Procedure:      writeLog
       * 
       * Description:    procedure logs all given information in table message_log
       *
       * Parameters:     i_v_moduleName        - name of calling module
       *                 i_n_moduleCodeLine    - line number in code of calling module
       *                                         for messages with given parameters
       *                 i_n_loggingLevel      - logging level of message
       *                 i_v_messageText       - free style message
       *                 i_b_setAppInfo        - TRUE = set application info (DBMS_APPLICATION_INFO)
       ********************************************************************************************************
    */
   PROCEDURE writeLog (i_v_moduleName       IN VARCHAR2,
                       i_n_moduleCodeLine   IN NUMBER,
                       i_n_loggingLevel     IN PLS_INTEGER,
                       i_v_messageText      IN VARCHAR2,
                       i_b_setAppInfo       IN BOOLEAN DEFAULT FALSE)
   IS
   BEGIN
      IF correctLoggingLevel (i_n_loggingLevel) THEN
         IF logCFG.logLevel >= i_n_loggingLevel THEN
            doWrite ( i_n_loggingLevel     => i_n_loggingLevel,
                      i_v_messageText      => i_v_messageText,
                      i_v_moduleName       => i_v_moduleName,
                      i_n_moduleCodeLine   => i_n_moduleCodeLine);
         END IF;

         IF i_b_setAppInfo THEN
            DBMS_APPLICATION_INFO.SET_ACTION (action_name => i_v_messageText);
         END IF;
      ELSE
         RAISE e_wrongLoggingLevel;
      END IF;
   EXCEPTION
      WHEN e_wrongLoggingLevel THEN
         raise_application_error(-20011, 'LogFileMgr: Wrong logging level '||TO_CHAR (i_n_loggingLevel)||' given.');
      WHEN OTHERS THEN
         raise;
   END writeLog;

   /* ********************************************************************************************************
      * Procedure:   writeLog
      * Description: Procedure logs all given information in table message_log
      *
      * Parameters:   i_n_moduleName        -  name of calling module
      *               i_n_moduleCodeLine    -  line number in code of calling module
      *                                        for messages with given parameters
      *               i_n_loggingLevel      -  logging level of message
      *               i_v_messageText       -  free style message
      *               i_n_dbErrorID         -  Error-ID of DB
      *               i_v_dbErrorText       -  error message
      ********************************************************************************************************
    */

   PROCEDURE writeLog (i_v_moduleName       IN VARCHAR2,
                       i_n_moduleCodeLine   IN NUMBER,
                       i_n_loggingLevel     IN PLS_INTEGER,
                       i_v_messageText      IN VARCHAR2,
                       i_n_dbErrorID        IN NUMBER,
                       i_v_dbErrorText      IN VARCHAR2)
   IS
   BEGIN
    IF correctLoggingLevel (i_n_loggingLevel) THEN
         IF logCFG.logLevel >= i_n_loggingLevel THEN
            doWrite ( i_n_loggingLevel     => i_n_loggingLevel,
                      i_v_messageText      => i_v_messageText,
                      i_v_moduleName       => i_v_moduleName,
                      i_n_moduleCodeLine   => i_n_moduleCodeLine,
                      i_n_dbErrorID        => i_n_dbErrorID,
                      i_v_dbErrorText      => i_v_dbErrorText);
         END IF;
      ELSE
         RAISE e_wrongLoggingLevel;
      END IF;
   EXCEPTION
      WHEN e_wrongLoggingLevel THEN
         raise_application_error(-20011, 'LogFileMgr: Wrong logging level '||TO_CHAR (i_n_loggingLevel)||' given.');
      WHEN OTHERS THEN
         raise;
   END writeLog;

   /*  ********************************************************************************************************
       * Procedure:   raiseError
       * Description:    Procedure logs all given information in table message_log and raise DB error
       *
       * Parameters:   i_n_moduleName        -  name of calling module
       *               i_n_moduleCodeLine    -  line number in code of calling module
       *                                        for messages with given parameters
       *               i_n_loggingLevel      -  logging level of message
       *               i_v_messageText       -  free style message
       *               i_n_dbErrorID         -  Error-ID of database
       *               i_v_dbErrorText       -  error message
       ********************************************************************************************************
    */
   PROCEDURE raiseError (i_v_moduleName       IN VARCHAR2,
                         i_n_moduleCodeLine   IN NUMBER,
                         i_n_loggingLevel     IN PLS_INTEGER,
                         i_v_messageText      IN VARCHAR2,
                         i_n_dbErrorID        IN NUMBER,
                         i_v_dbErrorText      IN VARCHAR2)
   IS
      e_raiseError   EXCEPTION;
   BEGIN
       IF correctLoggingLevel (i_n_loggingLevel) THEN
         IF logCFG.logLevel >= i_n_loggingLevel THEN
            doWrite ( i_n_loggingLevel     => i_n_loggingLevel,
                      i_v_messageText      => i_v_messageText,
                      i_v_moduleName       => i_v_moduleName,
                      i_n_moduleCodeLine   => i_n_moduleCodeLine,
                      i_n_dbErrorID        => i_n_dbErrorID,
                      i_v_dbErrorText      => i_v_dbErrorText);
         END IF;
         -- raise the error
         RAISE e_raiseError;
      ELSE
         RAISE e_wrongLoggingLevel;
      END IF;
   EXCEPTION
      WHEN e_wrongLoggingLevel THEN
         raise_application_error(-20011, 'LogFileMgr: Wrong logging level '||TO_CHAR (i_n_loggingLevel)||' given.');
      WHEN e_raiseError  THEN
         -- Now raising the error
         IF i_n_dbErrorID BETWEEN -20999 AND -20000 THEN
            RAISE_APPLICATION_ERROR (i_n_dbErrorID, NVL (i_v_dbErrorText, SUBSTRB (i_v_messageText, 1, 508)));
         ELSE
            RAISE_APPLICATION_ERROR (-20000, 'ERROR:' || TO_CHAR (i_n_dbErrorID) || '-' || i_v_dbErrorText);
         END IF;
      WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR ( -20020, 'Error in module = ' || i_v_moduleName || ', line='|| TO_CHAR (i_n_moduleCodeLine) || ' ' || SQLERRM);
   END raiseError;     
      
      
BEGIN
 logCFG.moduleName   := NULL;
 logCFG.logLevel     := NULL;
 logCFG.logFormat    := logFormatAll;
 logCFG.logHandle    := NULL;   
 logCFG.logFileName  := 'default.log';
 logCFG.logFileDir   := '/tmp/';
END LogFileMgr;
/