CREATE OR REPLACE PACKAGE cout_err IS

  g_header   CONSTANT VARCHAR2(160) := '$Header: /Isle Of Grain/Database/PLSQL/cout_err.pck 3     1/04/05 16:13 Hardya $';
  g_revision CONSTANT VARCHAR2(160) := '$Revision: 3 $';

  /**
    -- Package containing the common error utility modules used by the Access Manager application
    -- #version $Revision: 3 $
    -- #author Andy Hardy
  */

  /** Specifies logging to be table-based (ERROR_LOGS), this the default log method.*/
  c_table CONSTANT PLS_INTEGER := 1;
  /** Specifies logging to be file-based. */
  c_file CONSTANT PLS_INTEGER := 2;
  /** Specifies logging to be screen-based. */
  c_screen CONSTANT PLS_INTEGER := 3;

  /** Specifies the substitution values that could be passed into any generated error message */
  TYPE g_t_substitution_list IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
  c_empty_substitution_list g_t_substitution_list;

  /**
  -- Reports the error and RAISES an exception to halt program execution
  #param p_exception_number The exception to be raised, defaults to SQLCODE
  #param p_exception_message The message to be logged, defaults to the matching message from EXCEPTION_MESSAGES
  #param p_helper_call If TRUE, indicates that this report is being produced through a helper function
  #param p_error_group The error group that this error should be reported as a part of
  */
  PROCEDURE report_and_stop(p_exception_number  IN INTEGER := SQLCODE
                           ,p_exception_message IN VARCHAR2 := NULL
                           ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list
                           ,p_helper_call_level IN NUMBER := 0
                           ,p_error_group       IN VARCHAR2 := 'APPLICATION'
                           ,p_severity          IN VARCHAR2 := 'E'
                           ,p_source            IN VARCHAR2 := NULL);

  /**
  -- Reports the error and continues program execution
  #param p_exception_number The exception to be raised, defaults to SQLCODE
  #param p_exception_message The message to be logged, defaults to the matching message from EXCEPTION_MESSAGES
    #param p_helper_call If TRUE, indicates that this report is being produced through a helper function
      #param p_error_group The error group that this error should be reported as a part of
  */
  PROCEDURE report_and_go(p_exception_number  IN INTEGER := SQLCODE
                         ,p_exception_message IN VARCHAR2 := NULL
                         ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list
                         ,p_helper_call_level IN NUMBER := 0
                         ,p_error_group       IN VARCHAR2 := 'APPLICATION'
                         ,p_severity          IN VARCHAR2 := 'E'
                         ,p_source            IN VARCHAR2 := NULL);

  /**
  -- Allows the specification of the log destination
  #param p_target The destination (as defined by the constants c_table, c_file and c_screen
  #param p_dir If file has been chosen, allows the log directory to be specified
  #param p_file If file has been chosen, allows the log filename to be specified
  */
  PROCEDURE logto(p_target IN PLS_INTEGER
                 ,p_dir    IN VARCHAR2 := NULL
                 ,p_file   IN VARCHAR2 := NULL);

  /**
  -- Returns the current log destination.
  #return Returns a value matching c_table, c_file or c_screen
  */
  FUNCTION logging_to RETURN PLS_INTEGER;

  /**
  Return an identification string to link to the source control system.
  */
  FUNCTION about RETURN VARCHAR2;

END cout_err;
/
CREATE OR REPLACE PACKAGE BODY cout_err IS
  g_target PLS_INTEGER := c_table;
  g_file   VARCHAR2(2000) := 'err.log';
  g_dir    VARCHAR2(2000) := '/export/home/mcdonald/INBOX';

  PROCEDURE pl(p_msg VARCHAR2) IS
    l_start NUMBER := 1;
    l_len CONSTANT NUMBER := 255;
  BEGIN
    WHILE l_start <= length(p_msg) LOOP
      dbms_output.put_line(substr(p_msg
                                 ,l_start
                                 ,l_len));
      l_start := l_start + l_len;
    END LOOP;
  END pl;

  PROCEDURE who_called_me(p_owner             OUT VARCHAR2
                         ,p_name              OUT VARCHAR2
                         ,p_lineno            OUT NUMBER
                         ,p_caller_type       OUT VARCHAR2
                         ,p_helper_call_level IN NUMBER := 0) AS
    l_call_stack           VARCHAR2(4096) DEFAULT dbms_utility.format_call_stack;
    l_num                  NUMBER;
    l_found_stack          BOOLEAN DEFAULT FALSE;
    l_line                 VARCHAR2(255);
    l_handle               VARCHAR2(20);
    l_linenum              NUMBER;
    l_count                NUMBER := 0;
    l_required_stack_level NUMBER := 5;
  BEGIN
    --
    l_required_stack_level := l_required_stack_level + p_helper_call_level;
  
    LOOP
      l_num := instr(l_call_stack
                    ,chr(10));
      EXIT WHEN(l_count = l_required_stack_level OR l_num IS NULL OR
                l_num = 0);
      --
      l_line       := substr(l_call_stack
                            ,1
                            ,l_num - 1);
      l_call_stack := substr(l_call_stack
                            ,l_num + 1);
      --
      IF (NOT l_found_stack) THEN
        IF (l_line LIKE '%handle%number%name%') THEN
          l_found_stack := TRUE;
        END IF;
      ELSE
        l_count := l_count + 1;
        -- l_count = 1 is ME
        -- l_count = 2 is MY Caller
        -- l_count = 3 is Their Caller, etc
        IF (l_count = l_required_stack_level) THEN
          l_line := ltrim(l_line);
          -- handle is the first part
          l_handle := substr(l_line
                            ,1
                            ,instr(l_line
                                  ,' '));
          --
          -- Now, remove the object handle, then the white space from the call.
          l_line := substr(l_line
                          ,length(l_handle) + 1);
          l_line := ltrim(l_line);
          -- Now we can get the line number.
          l_linenum := to_number(substr(l_line
                                       ,1
                                       ,instr(l_line
                                             ,' ')));
          -- Remove the line number, and white space.
          l_line := substr(l_line
                          ,length(l_linenum) + 1);
          l_line := ltrim(l_line);
        
          IF (l_line LIKE 'pr%') THEN
            l_num := length('procedure ');
          ELSIF (l_line LIKE 'fun%') THEN
            l_num := length('function ');
          ELSIF (l_line LIKE 'package body%') THEN
            l_num := length('package body ');
          ELSIF (l_line LIKE 'pack%') THEN
            l_num := length('package ');
          ELSIF (l_line LIKE 'anonymous%') THEN
            l_num := length('anonymous block ');
          ELSE
            l_num := NULL;
          END IF;
          IF (l_num IS NOT NULL) THEN
            p_caller_type := ltrim(rtrim(upper(substr(l_line
                                                     ,1
                                                     ,l_num - 1))));
          ELSE
            p_caller_type := 'TRIGGER';
          END IF;
        
          l_line   := substr(l_line
                            ,nvl(l_num
                                ,1));
          l_num    := instr(l_line
                           ,'.');
          p_owner  := ltrim(rtrim(substr(l_line
                                        ,1
                                        ,l_num - 1)));
          p_name   := ltrim(rtrim(substr(l_line
                                        ,l_num + 1)));
          p_lineno := l_linenum;
        END IF;
      END IF;
    END LOOP;
  END who_called_me;

  PROCEDURE get_exception_message(p_exception_number  IN exception_messages.exception_number%TYPE
                                 ,p_message           IN OUT exception_messages.message%TYPE
                                 ,p_exme_type         IN OUT exception_messages.exme_type%TYPE
                                 ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list) IS
    l_index        PLS_INTEGER;
    l_err_msg      VARCHAR2(512);
    l_err_error    VARCHAR2(1);
    l_err_msg_type VARCHAR2(3);
    l_err_msgid    INTEGER;
    l_err_loc      VARCHAR2(240);
  BEGIN
  
    BEGIN
      SELECT to_char(abs(exception_number)) || ': ' || message
            ,exme_type
        INTO p_message
            ,p_exme_type
        FROM exception_messages
       WHERE exception_number = p_exception_number;
    EXCEPTION
      WHEN no_data_found THEN
        p_message   := 'UNABLE TO FIND MESSAGE FOR EXCEPTION ' ||
                       to_char(p_exception_number);
        p_exme_type := 'E';
    END;
  
    l_index := p_substitution_list.FIRST;
  
    LOOP
      EXIT WHEN l_index IS NULL;
      p_message := REPLACE(p_message
                          ,'<p' || l_index || '>'
                          ,p_substitution_list(l_index));
      l_index   := p_substitution_list.NEXT(l_index);
    END LOOP;
  
  END get_exception_message;

  PROCEDURE log(p_exception_number  IN PLS_INTEGER := NULL
               ,p_exception_message IN VARCHAR2 := NULL
               ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list
               ,p_helper_call_level IN NUMBER := 0
               ,p_error_group       IN VARCHAR2 := 'APPLICATION'
               ,p_severity          IN VARCHAR2 := 'E'
               ,p_source            IN VARCHAR2 := NULL) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_exception_number  PLS_INTEGER := nvl(p_exception_number
                                          ,SQLCODE);
    l_exception_message exception_messages.message%TYPE;
    l_exme_type         exception_messages.exme_type%TYPE := substr(p_severity
                                                                   ,1
                                                                   ,1);
    l_owner             VARCHAR2(240);
    l_name              VARCHAR2(240);
    l_lineno            NUMBER;
    l_caller_type       VARCHAR2(240);
  
  BEGIN
  
    IF p_exception_number BETWEEN - 20999 AND - 20001 THEN
      --
      -- application error, see if we have a matching EXCEPTION_MESSAGE
      --
      get_exception_message(p_exception_number  => l_exception_number
                           ,p_substitution_list => p_substitution_list
                           ,p_message           => l_exception_message
                           ,p_exme_type         => l_exme_type);
      l_exception_message := nvl(l_exception_message
                                ,p_exception_message);
    ELSE
      l_exception_message := nvl(p_exception_message
                                ,SQLERRM);
    END IF;
    IF p_source IS NULL THEN
      who_called_me(p_owner             => l_owner
                   ,p_name              => l_name
                   ,p_lineno            => l_lineno
                   ,p_caller_type       => l_caller_type
                   ,p_helper_call_level => p_helper_call_level);
    END IF;
  
    IF g_target = c_table THEN
      INSERT INTO error_logs
        (erlo_id
        ,error_type
        ,SOURCE
        ,severity
        ,ERROR_CODE
        ,error_message
        ,error_date
        ,error_status
        ,created_on
        ,created_by
        ,updated_on
        ,updated_by)
      VALUES
        (erlo_seq.NEXTVAL
        ,p_error_group
        ,nvl(p_source
            ,l_name || ' line ' || l_lineno)
        ,l_exme_type
        ,l_exception_number
        ,l_exception_message
        ,SYSDATE
        ,'N'
        ,SYSDATE
        ,0
        ,SYSDATE
        ,0);
    
      pl('Error log:' || l_caller_type || ',' || l_name || ',' || l_lineno || ',' ||
         l_exme_type || ',' || l_exception_number || ',' ||
         l_exception_message || ',' ||
         to_char(SYSDATE
                ,'mm/dd/yyyy hh24:mi:ss'));
    
    ELSIF g_target = c_file THEN
      DECLARE
        fid utl_file.file_type;
      BEGIN
        fid := utl_file.fopen(g_dir
                             ,g_file
                             ,'A');
        utl_file.put_line(fid
                         ,'Error log:' || p_error_group || ',' ||
                          l_caller_type || ',' || l_name || ',' ||
                          l_lineno || ',' || l_exme_type || ',' ||
                          l_exception_number || ',' || l_exception_message || ',' ||
                          to_char(SYSDATE
                                 ,'mm/dd/yyyy hh24:mi:ss'));
        utl_file.fclose(fid);
      EXCEPTION
        WHEN OTHERS THEN
          utl_file.fclose(fid);
      END;
    ELSIF g_target = c_screen THEN
      dbms_output.put_line('Error log:' || p_error_group || ',' ||
                           l_caller_type || ',' || l_name || ',' ||
                           l_lineno || ',' || l_exme_type || ',' ||
                           l_exception_number || ',' ||
                           l_exception_message || ',' ||
                           to_char(SYSDATE
                                  ,'mm/dd/yyyy hh24:mi:ss'));
    
    END IF;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(substr(SQLERRM
                                 ,1
                                 ,80));
      ROLLBACK;
  END;

  PROCEDURE logto(p_target IN PLS_INTEGER
                 ,p_dir    IN VARCHAR2 := NULL
                 ,p_file   IN VARCHAR2 := NULL) IS
  BEGIN
    g_target := p_target;
    g_file   := nvl(p_file
                   ,g_file);
    g_dir    := nvl(p_dir
                   ,g_dir);
  END;

  FUNCTION logging_to RETURN PLS_INTEGER IS
  BEGIN
    RETURN g_target;
  END;

  PROCEDURE RAISE(p_exception_number  IN PLS_INTEGER := NULL
                 ,p_exception_message IN VARCHAR2 := NULL
                 ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list) IS
    l_exception_number  PLS_INTEGER := nvl(p_exception_number
                                          ,SQLCODE);
    l_exception_message VARCHAR2(1000) := nvl(p_exception_message
                                             ,SQLERRM);
    l_exme_type         exception_messages.exme_type%TYPE;
  BEGIN
    IF l_exception_number BETWEEN - 20999 AND - 20000 THEN
      get_exception_message(p_exception_number  => l_exception_number
                           ,p_substitution_list => p_substitution_list
                           ,p_message           => l_exception_message
                           ,p_exme_type         => l_exme_type);
    
      l_exception_message := nvl(l_exception_message
                                ,p_exception_message);
    
      raise_application_error(l_exception_number
                             ,l_exception_message); /* Use positive error numbers -- lots to choose from! */
    ELSIF l_exception_number > 0
          AND l_exception_number NOT IN (1, 100) THEN
      raise_application_error(-20000
                             ,l_exception_number || '-' ||
                              l_exception_message); /* Can't EXCEPTION_INIT -1403 */
    ELSIF l_exception_number IN (100, -1403) THEN
      RAISE no_data_found; /* Re-raise any other exception. */
    ELSIF l_exception_number != 0 THEN
      EXECUTE IMMEDIATE 'DECLARE myexc EXCEPTION; ' ||
                        ' PRAGMA EXCEPTION_INIT (myexc, ' ||
                        to_char(l_exception_number) || ');' ||
                        'BEGIN RAISE myexc; END;';
    END IF;
  END;

  PROCEDURE handle(p_exception_number  IN PLS_INTEGER := NULL
                  ,p_exception_message IN VARCHAR2 := NULL
                  ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list
                  ,p_logerr            IN BOOLEAN := TRUE
                  ,p_reraise           IN BOOLEAN := FALSE
                  ,p_helper_call_level IN NUMBER := 0
                  ,p_error_group       IN VARCHAR2 := 'APPLICATION'
                  ,p_severity          IN VARCHAR2 := 'E'
                  ,p_source            IN VARCHAR2 := NULL) IS
  BEGIN
    IF p_logerr THEN
      log(p_exception_number
         ,p_exception_message
         ,p_substitution_list
         ,p_helper_call_level => p_helper_call_level
         ,p_error_group => p_error_group
         ,p_severity => p_severity
         ,p_source => p_source);
    END IF;
    IF p_reraise THEN
      cout_err.RAISE(p_exception_number
                    ,p_exception_message
                    ,p_substitution_list);
    END IF;
  END;

  PROCEDURE report_and_stop(p_exception_number  IN INTEGER := SQLCODE
                           ,p_exception_message IN VARCHAR2 := NULL
                           ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list
                           ,p_helper_call_level IN NUMBER := 0
                           ,p_error_group       IN VARCHAR2 := 'APPLICATION'
                           ,p_severity          IN VARCHAR2 := 'E'
                           ,p_source            IN VARCHAR2 := NULL) IS
  BEGIN
    handle(p_exception_number
          ,p_exception_message
          ,p_substitution_list
          ,TRUE
          ,TRUE
          ,p_helper_call_level => p_helper_call_level
          ,p_error_group => p_error_group
          ,p_severity => p_severity
          ,p_source => p_source);
  END report_and_stop;

  PROCEDURE report_and_go(p_exception_number  IN INTEGER := SQLCODE
                         ,p_exception_message IN VARCHAR2 := NULL
                         ,p_substitution_list IN g_t_substitution_list := c_empty_substitution_list
                         ,p_helper_call_level IN NUMBER := 0
                         ,p_error_group       IN VARCHAR2 := 'APPLICATION'
                         ,p_severity          IN VARCHAR2 := 'E'
                         ,p_source            IN VARCHAR2 := NULL) IS
  BEGIN
    handle(p_exception_number
          ,p_exception_message
          ,p_substitution_list
          ,TRUE
          ,FALSE
          ,p_helper_call_level => p_helper_call_level
          ,p_error_group => p_error_group
          ,p_severity => p_severity
          ,p_source => p_source);
  END report_and_go;

  FUNCTION about RETURN VARCHAR2 IS
  BEGIN
    RETURN g_header;
  END about;

END cout_err;
/
