CREATE TABLE COMLOG_APP_LOG
(
  SESSION_ID           NUMBER,
  LOG_LEVEL            VARCHAR2(32),
  ACTION_ORDER_ID      NUMBER,
  SESSION_MODULE       VARCHAR2(64),
  SESSION_ACTION       VARCHAR2(128),
  SESSION_CLIENT_INFO  VARCHAR2(512),
  ERROR_NO             NUMBER,
  ERROR_TEXT           VARCHAR2(512),
  NO_OF_RECORDS        NUMBER,
  SESSION_START        DATE,
  LOG_DT_START         DATE,
  LOG_DT_END           DATE,
  ACTION_SEC           NUMBER,
  SESSION_SEC          NUMBER
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
/

CREATE SEQUENCE S_COMLOG_APP_LOG_ID
  START WITH 3001
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 200
  NOORDER
/

CREATE OR REPLACE package 
              pkg_common_logging 
as
    -- pkg_common_logging
    -- 2009; Ingo Voland 
    -- 
    -- Package enthaelt Funktionalitaeten fuer common logging
    -- benoetigt: Tabelle COMLOG_APP_LOG
    --            Sequence s_COMLOG_APP_LOG_id
    --
    --
    
    --
    -- folgende loglevel koennen definiert werden: debug / info / error
    -- es werden nur logeintraege geschrieben, wenn der in der 
    -- session ein höherer oder gleicher loglevel gesetzte ist als der
    -- für den logeintrag definierte. Somit kann eine 'debug' funktionalitaet 
    -- implementiert werden, ohne das diese debug Eintraege permanent die Logtabelle
    -- fuellen
    --
    --  matrix: logeintrage werden geschrieben wenn:
    --
    -- Log Eintrag Loglevel              Session Log Level
    --                      error        info            debug
    --   error               x             x                x
    --   info                              x                x
    --   debug                                              x
    --
    -- der default loglevel ist 'ERROR'
    --
    set_debug constant pls_integer := 3;
    set_info constant pls_integer := 2;
    set_error constant pls_integer := 1;
          
          
    --
    -- diese prozeduren setzen den loglevel fuer eine session
    -- der wert bleibt aktiv solange die session offen ist 
    -- oder bis ein andere wert gesetzt wird
    --
    -- der loglevel kann in der session variable oder fix im package gesezt werden.
    -- vorzugsweise soll der loglevel aber in der session gesetzt werden 
    -- um hardcodierte loglevel zu verhindern
    --
    procedure set_log_level(loglevel in pls_integer default set_error);
    procedure set_log_level(loglevel in COMLOG_APP_LOG.log_level%type default 'ERROR');

   --
    -- diese prozeduren setzen die loginformationen fuer eine session
    -- der wert bleibt aktiv bis:
    --   * ein neuer wert gesetzte wird
    --   * ein reset_log aufruf erfolgt
    --
    -- die gesetzte werte werden ebenfalls in v$session angezeigt
    -- 
    -- Das setzten eines wertes schreibt noch keine Eintraege in die Logtabellen
    -- mit hilfe der gesezten werte fuer Modul und Action sind die Logeintraege
    -- zu spaeteren Zeiten sortier- und gruppierbar
    --
    --
    -- Modul:   ist typischer weise der Name der ausführenden Procedure
    -- Action:  ist ein Subprozess in einer Procedure, z.b. 'fuellen der tabelle xyz'
    -- client_info: ist eine beliebige information für die aktuelle ausgeführte operatiom
    --
    procedure set_module(module_name varchar2, action_name varchar2);
    procedure set_action(action_name varchar2);
    procedure set_client_info(client_info varchar2);
    
    
    
    -- init_log: immer der erste Aufruf, das Loging wird initialisert
    --           der eintrag wird immer im loglevel 'error' geschrieben
    --
    procedure init_log(module_name varchar2, action_name varchar2,client_info varchar2);
    
    -- write_log 
    --          setzt 'cleint_info' und schreibt einen eintrag mit dem angegebenen loglevel. 
    --          wird kein loglevel angegeben wird der eintrag unter dem aktuell in der session 
    --          gesetzten loglevel geschrieben, default ist 'error'
    --          zu beachten ist, das die logeintraege nur geschrieben werden wenn 
    --          der in der procedure angegebenen loglevel kleiner oder gleich 
    --          dem in der session gesetzten loglevel ist
    procedure write_log(client_info varchar2, loglevel pls_integer default null );
    
    -- write_log_ins 
    --          diese procedure schreibt die anzahl der in der vorherigen aktion durch insert eingefügten records
    --          in die logtabelle. Fuer den Loglevel gilt das unter 'write_log' gesagte
    --
    --      beispiel:
    --          insert into TEST values (1,'testlog');
    --          pkg_common_logging.write_log_ins(loglevel => pkg_common_logging.set_info);
    --
    --          oder ohne spezifizierten loglevel, dann wird der session-loglevel genommen 
    --          und ein insert in die logtabelle findet immer statt
    --          pkg_common_logging.write_log_ins;
    --
    procedure write_log_ins(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount, loglevel pls_integer default null);
    
    -- write_log_del 
    --          diese procedure schreibt die anzahl der in der vorherigen aktion durch delete  geloeschten records
    --          in die logtabelle. Fuer den Loglevel gilt das unter 'write_log' gesagte
    --
    --      beispiel:
    --          delete from test;
    --          pkg_common_logging.write_log_del(loglevel => pkg_common_logging.set_info);
    --
    --
    --          oder ohne spezifizierten loglevel, dann wird der session-loglevel genommen 
    --          und ein insert in die logtabelle findet immer statt
    --          pkg_common_logging.write_log_del;
    --
  

    procedure write_log_del(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount, loglevel pls_integer default null);
    
    -- write_log_upd 
    --          diese procedure schreibt die anzahl der in der vorherigen aktion durch insert eingefügten records
    --          in die logtabelle. Fuer den Loglevel gilt das unter 'write_log' gesagte
    --
    --      beispiel:
    --          update test set id = 3;
    --          pkg_common_logging.write_log_upd(loglevel => pkg_common_logging.set_info);
    --
    --          oder ohne spezifizierten loglevel, dann wird der session-loglevel genommen 
    --          und ein insert in die logtabelle findet immer statt
    --          pkg_common_logging.write_log_upd;
    --

    procedure write_log_upd(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount,loglevel pls_integer default null);
    
    -- write_log_sel 
    --          diese procedure schreibt die anzahl der in der vorherigen aktion durch insert eingefügten records
    --          in die logtabelle. Fuer den Loglevel gilt das unter 'write_log' gesagte
    --
    --      beispiel:
    --          select count(*) into v_num from dual;
    --          pkg_common_logging.write_log_sel(loglevel => pkg_common_logging.set_info);
    --
    --          oder ohne spezifizierten loglevel, dann wird der session-loglevel genommen 
    --          und ein insert in die logtabelle findet immer statt
    --          pkg_common_logging.write_log_sel;
    --
    
    procedure write_log_sel(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount, loglevel pls_integer default null);
    
    
    -- write_log_error 
    --          diese procedure schreibt die fehlernumer und fehlercode in die logtabelle ,
    --          wenn in einer procedure eine exception aufgetreten ist. der Loglevel ist immer 'ERROR'
    --          diese Procedure muss mit in das excaption handling der aufrufenden instanz eingebunden sein
    --
    --      beispiel:
    --
    --          declare
    --              v_num number;
    --          begin
    --              select 1 / 0 ito v_num from dual;
    --
    --          exception 
    --             when others then 
    --                 pkg_common_logging.write_log_error;
    --                 raise;
    --          end;    
    --         
    --
    
    
    procedure write_log_error(client_info varchar2 default null, error_no varchar2 default SQLCODE, error_info varchar2 default SQLERRM);
    
    
    --
    -- diese procedure wird üblicher weise am ende der abarbeitung einer procedure 
    -- oder eines subprozesses aufgerufen und erhoeht die SESSION_ID .
    -- weiterhin werden alle anderen zaehler und timestamps 
    -- neu initialisiert.
    --
    
    procedure reset_log;
    
    
 --
 -- interne vars
 --
    r_comlog_app_log  comlog_app_log%rowtype;
    gv_log_level pls_integer;
    
    function show_version return varchar2;
     
end pkg_common_logging;
/

CREATE OR REPLACE package body
              pkg_common_logging 
as
    
 c_version constant varchar2(32) := '01.00 / 20090802';
 c_curr_ACTION_ORDER_ID COMLOG_APP_LOG.ACTION_ORDER_ID%type ;
 type r_log_level is table of COMLOG_APP_LOG.LOG_LEVEL%type ;
 l_log_level r_log_level :=  r_log_level('ERROR','INFO','DEBUG');


 procedure reset_init
    is
    begin
          select s_COMLOG_APP_LOG_id.nextval into pkg_common_logging.r_comlog_app_log.SESSION_ID from dual;
        pkg_common_logging.r_comlog_app_log.SESSION_START := sysdate;
        pkg_common_logging.r_comlog_app_log.LOG_DT_START := sysdate;
        c_curr_ACTION_ORDER_ID := 0;
        pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := 0;
 
 end reset_init;
    
 
  procedure write(write_loglevel in pls_integer default set_error) is
   PRAGMA AUTONOMOUS_TRANSACTION;
   
  begin
    
  
  
    
     pkg_common_logging.r_comlog_app_log.LOG_DT_END := sysdate;
     pkg_common_logging.r_comlog_app_log.ACTION_SEC :=  round((pkg_common_logging.r_comlog_app_log.LOG_DT_END  
                                                        - pkg_common_logging.r_comlog_app_log.LOG_DT_START) *24*3600);
     pkg_common_logging.r_comlog_app_log.SESSION_SEC :=  round((pkg_common_logging.r_comlog_app_log.LOG_DT_END  
                                                        - pkg_common_logging.r_comlog_app_log.SESSION_START) *24*3600);
                                                        
   
   
                                                      
   if   write_loglevel <= pkg_common_logging.gv_log_level then            
     
     pkg_common_logging.r_comlog_app_log.LOG_LEVEL := l_log_level(write_loglevel);
     
     c_curr_ACTION_ORDER_ID := nvl( c_curr_ACTION_ORDER_ID,0) + 1;
     pkg_common_logging.r_comlog_app_log.ACTION_ORDER_ID :=  c_curr_ACTION_ORDER_ID;
                                                                                               
    insert into COMLOG_APP_LOG
        values pkg_common_logging.r_comlog_app_log;
    
    end if;
  
    pkg_common_logging.r_comlog_app_log.LOG_DT_START := sysdate;
    pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := 0;
  commit;
  
  
  end write;
  
  
  
    
    procedure set_log_level(loglevel in pls_integer default set_error) is
    
    begin
    
     pkg_common_logging.gv_log_level := loglevel;
     
     if nvl(pkg_common_logging.gv_log_level,0) not in (set_debug,set_info,set_error) then
         pkg_common_logging.gv_log_level := set_error;
     
     end if;
     

    
     pkg_common_logging.r_comlog_app_log.LOG_LEVEL := l_log_level(pkg_common_logging.gv_log_level);
          
    
       
       
    end set_log_level;
    
    procedure set_log_level(loglevel in COMLOG_APP_LOG.log_level%type default 'ERROR')
    
    is
    
    begin
     if upper(loglevel) in ('DEBUG','INFO','ERROR') then 
        pkg_common_logging.r_comlog_app_log.LOG_LEVEL := loglevel;
       
     else
         pkg_common_logging.r_comlog_app_log.LOG_LEVEL := 'ERROR' ;
     end if;                             
    
    
     pkg_common_logging.gv_log_level := case upper(loglevel)
                             when 'DEBUG' then set_debug
                             when 'INFO' then set_info
                             when 'ERROR' then set_error
                             else set_error                                 
                        end  ;
    
    end set_log_level;

    procedure set_module(module_name varchar2, action_name varchar2) is
    begin
    
    dbms_application_info.set_module(substr(module_name,1,32) ,substr(action_name,1,32));
    pkg_common_logging.r_comlog_app_log.SESSION_MODULE := substr(module_name,1,64);
    pkg_common_logging.r_comlog_app_log.SESSION_ACTION := substr(action_name,1,128);
    
    end set_module;
    
    
    procedure set_action(action_name varchar2) is
    begin
      dbms_application_info.set_action(substr(action_name,1,32) );
      pkg_common_logging.r_comlog_app_log.SESSION_ACTION := substr(action_name,1,128);
 
    end set_action;
    
    procedure set_client_info(client_info varchar2) is
    begin
    
    dbms_application_info.set_client_info( substr(client_info,1,32) );
      pkg_common_logging.r_comlog_app_log.SESSION_CLIENT_INFO := substr(client_info,1,512);
 
    end set_client_info;
    
    
    procedure init_log(module_name varchar2, action_name varchar2,client_info varchar2
      )
    is
    
    begin
        reset_init;
        
        pkg_common_logging.r_comlog_app_log.SESSION_START := sysdate;
        pkg_common_logging.r_comlog_app_log.LOG_DT_START := sysdate;
        
        set_module(module_name,action_name);
        set_client_info(client_info);
        pkg_common_logging.r_comlog_app_log.SESSION_CLIENT_INFO := 'INIT: '||client_info;
    
        write(set_error);
    
    
    end init_log;  
      
      
      
    procedure write_log(client_info varchar2, loglevel pls_integer default null )
    is
    begin
        set_client_info(client_info);
        write(nvl(loglevel,pkg_common_logging.gv_log_level));
    
    end write_log;
    
    
    procedure write_log_ins(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount,loglevel pls_integer default null) 
    is
     begin
     pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := NO_OF_RECORDS;
     set_client_info('INS: '||to_char(NO_OF_RECORDS)||' Records');
     write(nvl(loglevel,pkg_common_logging.gv_log_level));
     end write_log_ins;
 
  procedure write_log_sel(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount,loglevel pls_integer default null) 
    is
     begin
     pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := NO_OF_RECORDS;
     set_client_info('SEL: '||to_char(NO_OF_RECORDS)||' Records');
     write(nvl(loglevel,pkg_common_logging.gv_log_level));
     
     end write_log_sel;
     
         
    procedure write_log_del(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount,loglevel pls_integer default null)
    is
    
    begin
    set_client_info('DEL: '||to_char(NO_OF_RECORDS)||' Records');
    
     pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := NO_OF_RECORDS;
    
     write(nvl(loglevel,pkg_common_logging.gv_log_level));
    end write_log_del;
    
    procedure write_log_upd(NO_OF_RECORDS in COMLOG_APP_LOG.NO_OF_RECORDS%type  default sql%rowcount,loglevel pls_integer default null)
    is
      begin
      pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := NO_OF_RECORDS;
    set_client_info('UPD: '||to_char(NO_OF_RECORDS)||' Records');
     write(nvl(loglevel,pkg_common_logging.gv_log_level));
    end write_log_upd;
    
    
    procedure write_log_error(client_info varchar2 default null, error_no varchar2 default SQLCODE, error_info varchar2 default SQLERRM )
    is
    begin
    
    if client_info is null then 
        set_client_info('ERR: '||pkg_common_logging.r_comlog_app_log.SESSION_CLIENT_INFO );
     else
      set_client_info(client_info);
    end if;
    
    pkg_common_logging.r_comlog_app_log.ERROR_NO := error_no; 
    pkg_common_logging.r_comlog_app_log.ERROR_TEXT := SUBSTR (error_info, 1, 512);
    write(set_error);
    
    pkg_common_logging.r_comlog_app_log.ERROR_NO := null; 
    pkg_common_logging.r_comlog_app_log.ERROR_TEXT := null;
    
    end write_log_error;
    
    
   
    
    procedure reset_log
    is
    
    
    begin
   
       -- write ende    
       pkg_common_logging.r_comlog_app_log.LOG_DT_END := sysdate;
       set_client_info('End');
       set_action('End');
       write;
       
       -- reset 
       reset_init;
       
     
    
    end reset_log;
    
     procedure reset_log_soft
    is
    
    
    begin
    
          if  pkg_common_logging.r_comlog_app_log.SESSION_ID is null 
        then
           select s_COMLOG_APP_LOG_id.nextval into pkg_common_logging.r_comlog_app_log.SESSION_ID from dual;
            
        end if;
    
        if  pkg_common_logging.r_comlog_app_log.SESSION_START is null 
        then
            pkg_common_logging.r_comlog_app_log.SESSION_START := sysdate;
        end if;
    
        c_curr_ACTION_ORDER_ID := 0;
        pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := 0;
    
    end reset_log_soft;

function show_version return varchar2
is

begin

    return 'pkg_common_logging version is: '||c_version;
end show_version;


  begin
 -- check for session_id
 
  
    reset_log_soft;
    set_log_level(pkg_common_logging.gv_log_level);
    c_curr_ACTION_ORDER_ID := 0;
    
        

    end pkg_common_logging;
/

