How to Use autonomous transactions to store log tables in Oracle

A database transaction is a unit operation. Either all operations are successful or all operations fail. In Oracle, a transaction starts from executing the first data governance language (DML) statement until it executes a COMMIT statement, commits and saves the transaction, or executes a ROLLBACK statement, stop this operation.

It is difficult to record the error information to the database table because the transaction fails to be re-run, the INSERT statement used to write log entries has not been completed yet.

To address this dilemma, Oracle provides a convenient method, that is, autonomous transactions. An autonomous transaction starts from the current transaction and runs in its own context. They can be submitted or re-run independently without affecting running transactions. As a result, they form an ideal form of writing error log tables. When an error is detected in a transaction, you can insert a row in the error log table and submit it, and then roll back the primary transaction without losing this insert.

Because the autonomous transaction is separated from the primary transaction, it cannot detect the current status of the modified row. This seems that they have been in separate sessions before the primary transaction is committed, and they are unavailable for autonomous transactions. However, in turn, the situation is different: the main transaction can detect the results of self-governing transactions that have been executed.

To create an autonomous transaction, you must use the PRAGMA AUTONOMOUS_TRANSACTION statement in PL/SQL at the top of the anonymous block or in the stored procedure, function, data packet, or trigger definition section. The SQL Server statements executed in such a module or process are autonomous.

The trigger cannot contain the COMMIT statement, unless the PRAGMA AUTONOMOUS_TRANSACTION flag exists. However, only the statements in the trigger can be committed, but not the primary transaction.

List A shows the create table and create sequence statements for A simple but flexible error log TABLE. Create table errorlog (

Errorlog_id NUMBER,

Logged_on timestamp default policimestamp,

Logged_by VARCHAR2 (30) default user,

Num1 NUMBER,

Num2 NUMBER,

Num3 NUMBER,

Text1 VARCHAR2 (1000 ),

Text2 VARCHAR2 (1000 ),

Text3 VARCHAR2 (1000)

);

Create sequence errorlog_seq

Start with 1

Increment by 1;

List B is an independent stored procedure used to update Error Log tables. CREATE OR REPLACE

PROCEDURE log_error (

N1 in number: = NULL, t1 in varchar: = NULL,

N2 in number: = NULL, t2 in varchar: = NULL,

N3 in number: = NULL, t3 in varchar: = NULL

)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

Insert into errorlog

(Errorlog_id,

Num1, num2, num3, text1, text2, text3)

VALUES

(Errorlog_seq.NEXTVAL, n1, n2, n3, t1, t2, t3 );

COMMIT;

END;

This process accepts up to three numbers and three text variables, and stores them in the table together with the timestamp and the user who calls the process.

To test this process, you must UPDATE or DELETE some rows in the table. This triggers the primary transaction. Then execute the stored procedure and pass the data you selected to log to it. Finally, run the master transaction again and SELECT the (SELECT) error log table. Your log entries will still be there.