ORACLE PL/SQL uses autonomous transactions to achieve logging
The logging function is usually implemented in the program, especially the error log when an error occurs in a transaction. If the log is recorded in the database, it can facilitate subsequent query and analysis. However, if the total capacity of logging is written directly in the transaction, if ROLLBACK occurs in the transaction, the recorded log will also have ROLLBACK, which is obviously not appropriate. However, the use of autonomous transactions can easily implement the logging function, and will not be affected by the main transaction. Let's implement a simple logging example.
1. Create a logging table
Click (here) to collapse or open
Create table logtab (
Code integer
Text varchar2 (4000)
Created_on date
Created_by varchar2 (50)
Changed_on date
Changed_by varchar2 (50)
); 2. Create a package for logging
Creating a log package makes it easy to manage logging in a unified manner and enables autonomous transactions.
Log package contains two procedure:putline and saveline,putline to implement log insertion, saveline is an autonomous transaction, call putline. The saveline implementation can be called directly when logging in the program.
Click (here) to collapse or open
Create or replace package log
Is
Procedure putline (code_in in integer, text_in in varchar2)
Procedure saveline (code_in in integer, text_in in varchar2)
End log
/
Create or replace package body log
Is
Procedure putline (
Code_in in integer,text_in in varchar2)
Is
Begin
Insert into logtab
Values (code_in,text_in,sysdate,user,sysdate,user)
End
Procedure saveline (
Code_in in integer,text_in in varchar2)
Is
Pragma autonomous_transaction
Begin
Putline (code_in, text_in)
Commit
Exception when others then rollback
End
End log
/ 3. A simple example.
Select a data that does not exist and view the records in logtab
Click (here) to collapse or open
Declare
Sal pls_integer
Begin
Select salary into sal from employees where employee_id = 11111
Exception
When others
Then sys.log.saveline (sqlcode,sqlerrm)
End
/ check the records in logtab
Click (here) to collapse or open
Select * from logtab
CODE TEXT CREATED_O CREATE_BY CHANGE_ON CHANGE_BY
100 ORA-01403: no data found 10-JUN-18 SYS 10-JUN-18 SYS
