How to Use autonomous transactions to store log tables in Oracle

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.