How to save the log table with autonomous transactions in Oracle

A database transaction is a unit operation, either all operations succeed or all fail. In Oracle, a transaction begins with executing the first Data Governance language (DML) statement until a commit statement is executed, commits to save the transaction, or executes a rollback statement that discards the operation.

The nature of the "either complete or incomplete" of the transaction makes it difficult to log the error message into the database table because the INSERT statement used to write the journal entry is not completed when the transaction fails again.

In response to this dilemma, Oracle provides an easy way to do this, namely autonomous transactions. The autonomous transaction begins with the current transaction and executes in its own context. They can be committed or rerun independently, without affecting the running transaction. Because of this, they are the ideal form of writing error log forms. 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 master transaction without losing this insertion.

Because the autonomous transaction is separate from the main transaction, it cannot detect the current state of the modified row. It seems that they are in separate sessions until the main transaction is committed, and they are not available for autonomous transactions. However, the reverse is different: The master transaction can detect the results of an autonomous transaction that has already been performed.

To create an autonomous transaction, you must use the Pragma autonomous_transaction statement in Pl/sql in the top-level of the anonymous block or in a stored procedure, function, packet, or trigger definition section. SQL Server statements executed in such a module or procedure are autonomous.

The trigger cannot contain a COMMIT statement unless there is a pragma autonomous_transaction tag. However, only the statements in the trigger can be committed, not the master 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 Systimestamp,

Logged_by VARCHAR2 () 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 a separate stored procedure that updates the error log table. 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 procedure accepts up to three digits and three text variables, and then stores them in the table with the timestamp and the user who invoked the procedure.

To test this procedure, update (update) or delete some rows in the (delete) table; This raises the master transaction. Then execute the stored procedure and pass it to the data that you selected to log. Finally, rerun the master transaction, select the (select) Error log form, and your log entry will still be there.