Simplified PL/SQL debugging record consistency error logs

I have been working on Oracle databases for more than a year. My favorite part is PL/SQL. This is Oracle's data programmable language, which can be used to combine procedural code with SQL code.

The most interesting thing is that we can use it to develop the entire application in one place. All data selection and updates, all HTTP and HTML outputs that interact with LDAP, and everything else can be done in one language.

However, the disadvantage is that the application is very complicated. Because a specific process or function in PL/SQL code may need to complete any of the above tasks, it is very difficult to debug the search error when a problem occurs.

Is this a database issue or an HTTP issue when using LDAP? Where is the problem? In each PL/SQL project, I like to use a simple error logging mechanism. In this way, when a problem occurs, I have a simple data table that can be viewed to track all errors.

I will start by building a log data table:

CREATE TABLE ERRORLOG ( IDNUMBERNOT NULL, TIMEDATENOT NULL, CODEVARCHAR2(9 BYTE)NOT NULL, MESSAGEVARCHAR2(2000 BYTE)NOT NULL, PACKAGE_NAMEVARCHAR2(100 BYTE), PROCEDURE_NAMEVARCHAR2(100 BYTE)NOT NULL, LOCATIONNUMBER, PARAMETERSVARCHAR2(4000 BYTE) );

Unlike most other databases, Oracle does not have a built-in field for automatically adding IDs. Therefore, we need to create a sequence, and then add some mechanisms to assign a sequence value when a new value is inserted. Below is our sequence:

CREATE SEQUENCE ERRORLOG_ID_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

We can use the trigger to insert a timestamp, which will be implemented in the package that provides the log interface later. The trigger completes the operation by using the sequence ID field created above.

CREATE OR REPLACE TRIGGER T_ERRORLOG_BI BEFORE INSERT ON ERRORLOG REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW begin if :new.id is null then select errorlog_id_seq.nextval into :new.id from dual; end if; end t_errorlog_bi;

In practical applications, it is very helpful to provide a primary key for a data table. I know that we can set a primary key when creating a table, but I like to do this separately. I don't know why, but I feel more clear about it.

ALTER TABLE ERRORLOG ADD (PRIMARY KEY (ID));

PL/SQL is based on the ancient Ada programming language. Therefore, when creating a package, you need to provide two parts: the Specification Description Interface) and the real implementation of the Main Part interface. For our simple error logging package, the Specification section only provides a single process:

CREATE OR REPLACE package pkg_error is procedure log (p_error_code errorlog.code%type, p_error_message errorlog.message%type, p_package errorlog.package_name%type default null, p_procedure errorlog.procedure_name%type, p_location errorlog.location%type default null, p_parameters errorlog.parameters%type default null); end pkg_error;

The main body can contain any number of processes and functions you want. At least, it must implement the process and function in the Specification Description. In this example, the main part of the package only implements one process in the Specification Description:

CREATE OR REPLACE package body pkg_error is procedure log (p_error_code errorlog.code%type, p_error_message errorlog.message%type, p_package errorlog.package_name%type default null, p_procedure errorlog.procedure_name%type, p_location errorlog.location%type default null, p_parameters errorlog.parameters%type default null) is pragma autonomous_transaction; begin insert into errorlog (time, code, message, package_name, procedure_name, location, parameters) values (sysdate, p_error_code, p_error_message, p_package, p_procedure, p_location, p_parameters); commit; end log; end pkg_error;

The pragma autonomous_transaction section is very important because you ensure that the log method can submit the log data to the data table. Otherwise, you need to submit any information related to errors during the submission process. This is the function completed by The pragma segment. It tells Oracle to use this process as an atomic operation and does not affect any information in the early call stack.

Of course, the last step is to apply the log information to the code. For example, if you have a simple small function, and you want to record it whenever an exception occurs, you also want to avoid another error. In other words, record errors and enable the application to continue running. You may need to do the following:

CREATE OR REPLACE FUNCTION hello_world RETURN VARCHAR2 IS v_ procedure errorlog.procedure_name%TYPE default 'hello_world'; v_ location errorlog.location%TYPE; BEGIN v_location := 1000; return ('hello, world'); EXCEPTION when others then pkg_error.log(p_error_code => substr(sqlerrm,1,9), p_error_message => substr(sqlerrm,12), p_package => 'FUNCTION', p_procedure => v_procedure, p_location => v_location); END;

Obviously, if it is in the package, you need to create a global variable based on the actual name of the package you created, and pass it to the p_package parameter to replace the String constant we use here.

This is very similar. I don't want to say this is the best way to record errors in PL/SQL. I have been engaged in this field for more than a year, so I am not an expert in any case.

However, this logging method makes debugging my application very easy. If an error occurs, we have a record of all the error processes and functions. Therefore, we can quickly track the root cause of the problem.

Related Articles]

• PL/SQL basics: hierarchical Query

• Summary of Oracle Database PL/SQL encoding rules

• Using PL/SQL to operate COM objects in Oracle