Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.