[Oracle] audit functions using autonomous transactions

Source: Internet
Author: User

One or several autonomous transactions can be defined in a transaction (outer transaction. An autonomous transaction can be committed independently without affecting the outer transaction. Similarly, the rollback of the outer transaction does not affect the autonomous transaction.
 
If you have such a requirement, you cannot perform certain operations (such as update) on a table. Once such operations are performed, the entire rollback is forced, in addition, this violation operation is recorded in the audit log. Now the problem is that after the transaction is rolled back, the records in the log will also be rolled back, and autonomous transactions can be used to prevent this problem.
 
First, define a log table for audit.

Create table error_logs (
Id number (10) not null,
Log_timestamp timestamp not null,
Error_message varchar2 (4000)
);

Create sequence error_log_seq;

Creates a stored procedure for an autonomous transaction (the keyword PRAGMA AUTONOMOUS_TRANSACTION represents an autonomous transaction), used to insert error information to the audit table.

Create or replace procedure log_errors (p_error_message IN varchar2)
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert into error_logs (id, log_timestamp, error_message)
Values (error_log_seq.NEXTVAL, systimestamp, p_error_message );
Commit;
END;
/

Create a test table. The ID field of the table is not empty.

Create table at_test (
Id number (10) not null,
Des varchar2 (200)
);

Define a stored procedure and try to insert invalid data into the test table

Create or replace procedure p1
BEGIN
Insert into at_test (id, des)
Values (1, 'sc1 ');
Insert into at_test (id, des)
Values (NULL, 'desc2 ');
EXCEPTION
WHEN OTHERS THEN
Log_errors (p_error_message => SQLERRM );
Rollback;
END;
/

Run the stored procedure to check whether the audit is successful.

SQL> exec p1

The PL/SQL process is successfully completed.

SQL> select * from at_test;

Unselected row

SQL> select * from error_logs;

ID LOG_TIMESTAMP ERROR_MESSAGE
-------------------------------------------------------------------------------------------------
ORA-01400: cannot insert NULL ("TEST". "AT_TEST". "ID ")

From the above results, we can find that the transaction that inserts data to the at_test table is rolled back normally, but the audit autonomous transaction is not rolled back.

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.