oracle plsql logging - ghdrako/doc_snipets GitHub Wiki

Own maintain logging

CREATE TABLE LOGGING (
Logging_ID NUMBER GENERATED ALWAYS AS IDENTITY,
EVENT_DATE TIMESTAMP NOT NULL,
ACTION_MESSAGE VARCHAR2(255),
CODE_LOCATION VARCHAR2(255)
);
COMMIT;

create or replace PACKAGE loggingPackage
AS
PROCEDURE LOG_ACTION(
action_message IN VARCHAR2, code_location IN VARCHAR2);
END loggingPackage;



create or replace PROCEDURE LOG_ACTION
(action_message IN logging.action_message%TYPE,
code_location IN logging.code_location%TYPE)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;

    err_num NUMBER;
    err_msg VARCHAR2(100);
BEGIN
    INSERT INTO LOGGING
           (EVENT_DATE
          , action_message
          , code_location
           )
    VALUES
          (SYSDATE
         , action_message
         , code_location
          );
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SUBSTR(SQLERRM, 1, 100);
        EMERGENCY_OUTPUT(err_num, err_msg,DBMS_UTILITY.FORMAT_CALL_STACK);
        ROLLBACK;
END;

CREATE OR REPLACE DIRECTORY EXT_DIR AS '/emergency_dir';
CREATE OR REPLACE PROCEDURE EMERGENCY_OUTPUT(err_num IN NUMBER,
err_msg IN VARCHAR2, call_stack IN VARCHAR2)
AS
    output_file UTL_FILE.FILE_TYPE;
BEGIN
   output_file := UTL_FILE.FOPEN('EXT_DIR', 'emergency.txt' , 'A');
   IF UTL_FILE.IS_OPEN(output_file) THEN
       UTL_FILE.PUT_LINE(output_file, SYSTIMESTAMP || ' A platform issue may have occurred - error number - ' || err_num || ' Error message:
' || err_msg || ' Callstack message: ' || call_stack);
   END IF;
   IF UTL_FILE.IS_OPEN(output_file) THEN
       UTL_FILE.FCLOSE(output_file);
   END IF;
END;

In order for PL/SQL code to be able to write to a file in the emergency_dir directory, the directory ownership has to change from root to racle.

BEGIN
loggingPackage.LOG_ACTION('A log message', 'Trouble at mill');
END;


WHEN INVALID_CURSOR THEN
    LOGGINGPACKAGE.LOG_ACTION('process_project_data','INVALID_CURSOR');
    ROLLBACK;
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
    loggingPackage.LOG_ACTION(('Ouch', 'we hit an exception');
    ROLLBACK;
WHEN OTHERS THEN -- handles all other errors
   err_num := SQLCODE;
   err_msg := SUBSTR(SQLERRM, 1, 100);
   callstack_msg := DBMS_UTILITY.FORMAT_CALL_STACK;
   loggingPackage.LOG_ACTION('We hit a general exception error number
- '
|| ' ' || err_num || ' Error message: ' || err_msg,
'update_employees'
|| ' Callstack message: ' || callstack_msg);
   ROLLBACK;

FORMAT_CALL_STACK function from the DBMS_UTILITY package - recording where in the code the exception occurs.

Alternative logging

The sys.dbms_system.ksdwrt Procedure

Procedure sys.dbms_system.ksdwrt to write to trace files, alert logs, or both. This procedure has been widely used, but unfortunately, it is not documented and even has a wrapped declaration.

sys.dbms_system.ksdwrt(
  dest in binary_integer,
  tst  in varchar2)

where dest is: . Write to trace file . Write to alertlog . Write to both

The dbms_adr and dbms_adr_app Packages

These packages contain procedures for creating alert messages and incidents in the Automatic Diagnostic Repository (ADR, alert.log is a part of ADR). Although these procedures are not documented, we can still read their declaration section with a short description in the package specification. Some of the procedures include:

  1. write_trace
  2. write_log
  3. create_incident

The Error Stack

A trace event is a diagnostic feature that allows you to gather additional information about the behavior of your code at runtime. To dump the errorstack, you can execute the following command:

execute immediate q'[ALTER SESSION SET EVENTS 'immediate trace name errorstack level 3']';

Alternatively, if you only need to write a short message to the trace log, you can execute the following command:

execute immediate q'[alter session set events 'immediate trace("Something is wrong here...")']';

To learn more, we can run oradebug doc in sqlplus as a sys user

oradebug doc event action errorstack