Introduction
In complex IT environments, especially those utilizing robust database management systems like Oracle, effective log recording is crucial. Logs are not only essential records for maintenance and troubleshooting but are also fundamental for system security and auditing. However, implementing a log system that is both comprehensive and efficient can pose a significant challenge.
In this article, I present a custom solution I developed for generically recording logs in the database. Through a combination of a well-structured log table and a package, this solution not only simplifies the recording of essential information but also optimizes the process for different types of applications and monitoring needs. This solution is designed to be easily integrated into any process within the Oracle environment, offering a detailed and valuable insight into internal operations, errors, and procedure executions.
Throughout this article, I will detail the structure of the log table and how the Oracle PKG_TRACE_LOGS package is used to efficiently and securely record logs.
Solution Structure and Implementation
To illustrate the proposed solution, consider the image below:
Below, I will describe how you can achieve this result.
Table
The TRACED_LOGS table is designed to store detailed information about operations performed in your database. Each column in the table serves a specific purpose:
ID_TRACED_LOGS: A primary key that uniquely identifies each log entry. It is automatically filled by the associated trigger to ensure uniqueness.
PROCESS_NAME: Describes the name of the process being recorded. Allows easy reference to the type of activity or procedure that generated the log.
ERROR_DESCRIPTION: Stores custom error messages during process execution. Essential for diagnostics and failure analysis.
ORACLE_ERROR: Specifically stores errors generated by Oracle, formatted to facilitate the reading and identification of specific problems.
EXCEPTION_LINE: This column is particularly valuable for error analysis as it stores the complete error_backtrace. The error_backtrace does not just indicate where the error occurred; it provides a full "trail" of function or procedure calls that led to the error. This detailed record is essential for understanding the operational context in which the error happened.
PARAMETERS: Captures any parameters that were passed to the process at the time of execution, useful for reproducing and diagnosing problems.
EXECUTION_DATE: Date and time of the log execution, important for tracking when events occurred.
EXECUTION_USER: Identifies the user who executed the process, vital for audit and security issues. The structure of this table is crucial to ensure that all necessary aspects of each process are recorded, allowing for comprehensive analysis later on.
CREATE TABLE "TRACED_LOGS"
( "ID_TRACED_LOGS" NUMBER NOT NULL ENABLE,
"PROCESS_NAME" VARCHAR2(70),
"ERROR_DESCRIPTION" VARCHAR2(4000),
"ORACLE_ERROR" VARCHAR2(250),
"EXCEPTION_LINE" VARCHAR2(4000),
"PARAMETERS" VARCHAR2(4000),
"EXECUTION_DATE" DATE,
"EXECUTION_USER" VARCHAR2(50),
CONSTRAINT "TRACED_LOGS_PK" PRIMARY KEY ("ID_TRACED_LOGS")
USING INDEX ENABLE
)
/
/*
Sequence e trigger just for fill some basics fields like ID execution date and execution user.
*/
CREATE SEQUENCE TRACED_LOGS_SEQ START WITH 1 INCREMENT BY 1
/
CREATE OR REPLACE EDITIONABLE TRIGGER TRG_TRACED_LOGS
before insert on TRACED_LOGS
for each row
begin
:NEW.oracle_error := upper(:NEW.oracle_error);
if :NEW."ID_TRACED_LOGS" is null then
select "TRACED_LOGS_SEQ".nextval into :NEW."ID_TRACED_LOGS" from sys.dual;
end if;
if :new.EXECUTION_USER is null then
:new.EXECUTION_USER := nvl(APEX_APPLICATION.G_USER,user);
end if;
if :new.EXECUTION_DATE is null then
:new.EXECUTION_DATE := sysdate;
end if;
end;
/
Package
I typically store system procedures grouped by packages, so I normally have a package just for logging, which includes more than one procedure. However, as today I will only present one procedure, it is up to you to decide whether to create the suggested package or just the procedure that I will describe below.
The PKG_TRACE_LOGS package plays a key role in capturing and recording log information in processes, providing a structured interface for inserting this data into the TRACED_LOGS table. Below, we will detail how the PRC_GET_PROCESS_LOG procedure works and how it is used to record logs efficiently and systematically.
Package Definition:
create or replace package "PKG_TRACE_LOGS" as
PROCEDURE PRC_GET_PROCESS_LOG( P_PROCESS_NAME IN VARCHAR2
,P_ERROR IN VARCHAR2 default null
,p_format_error_backtrace in varchar2
,P_PARAMETERS IN VARCHAR2);
end "PKG_TRACE_LOGS";
Procedure PRC_GET_PROCESS_LOG
This procedure is designed to record detailed information about events and errors that occur during the execution of processes in the system. It accepts several parameters that allow capturing a detailed record of activities, as described below:
P_PROCESS_NAME: The name of the process being logged. This parameter identifies which operation or function is being executed, facilitating the filtering and analysis of specific logs.
P_FORMAT_ERROR_BACKTRACE: Captures the error trail generated by the function dbms_utility.format_error_backtrace. This parameter is crucial to understand the sequence of calls that led to the error, providing a detailed view of the execution path up to the point of failure.
P_ERROR: A string that describes the custom error that occurred, typically combined with sqlerrm to include the standard Oracle error message. This parameter is essential for diagnosing the specific problem that occurred.
P_PARAMETERS: Additional details about relevant parameters or variables at the time of the error. For example, if an error occurs when fetching a client's name, this field might include the client ID, which assists in investigation and error reproduction.
Package Body:
create or replace package body "PKG_TRACE_LOGS" as
PROCEDURE PRC_GET_PROCESS_LOG(P_PROCESS_NAME IN VARCHAR2
,P_ERROR IN VARCHAR2 default null
,p_format_error_backtrace in varchar2
,P_PARAMETERS IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; -- To isolate the transaction that will occur with the commit below
BEGIN
INSERT INTO TRACED_LOGS(PROCESS_NAME
,ERROR_DESCRIPTION
,ORACLE_ERROR
,EXCEPTION_LINE
,PARAMETERS
)
VALUES
(
P_PROCESS_NAME
,P_ERROR
,substr(P_ERROR, INSTR(upper(P_ERROR),'ORA'),3000)
,p_format_error_backtrace
,P_PARAMETERS
);
COMMIT;
END PRC_GET_PROCESS_LOG;
end "PKG_TRACE_LOGS";
Practical Use Example
The use of the PRC_GET_PROCESS_LOG procedure can be exemplified by the recording of an error that occurred in the function to obtain a customer's name:
declare
l_customer_name varchar2(100);
l_customer_id number := 999; -- There is no customer with this ID
begin
select full_name into l_customer_name from CUSTOMERS where customer_id = l_customer_id;
dbms_output.put_line(l_customer_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
PKG_TRACE_LOGS.PRC_GET_PROCESS_LOG(P_PROCESS_NAME => 'FNC_GET_CUSTOMER_NAME'
,p_format_error_backtrace => dbms_utility.format_error_backtrace
,P_ERROR => 'Error on getting customer full name. '||sqlerrm
,P_PARAMETERS => 'Customer ID: '|| l_customer_id);
end;
In this example, the call of the procedure registers in detail the error that occurred during the attempt to obtain the full name of a customer. The recorded information not only indicates which function failed but also details the sequence of events that led to the error and identifies the affected customer, which is vital for quick and effective corrections. The result generated was shown in the image above, at the beginning of the article.
This is a simple function, there are not many “traces” left when the error occurs. In more complex cases the EXCEPTION_LINE column will store something similar to this:
Conclusion
Throughout this article, we have explored a robust solution for logging in the database, addressing the structure of the TRACED_LOGS table and the detailed functionality of the PKG_TRACE_LOGS package. Through the implementation of these components, we have demonstrated how it is possible to effectively and systematically capture crucial information about operations and errors, facilitating analysis and problem resolution in complex IT environments.
The generic approach adopted allows for easy and adaptable integration into various processes and systems, ensuring not only improved diagnostics and maintenance but also enhancing security and compliance through detailed and auditable logs. It is important to note that the proposed model can be adapted and expanded as organizational needs evolve.
I hope that the implementation of this solution can assist you in managing your systems. I look forward to your feedback and discussion on this approach to continue refining and improving Oracle log recording practices.