Oracle Autonomous Transaction Example

Oracle Autonomous Transaction Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 3 mins read

In Oracle, an autonomous transaction can commit or rollback the data in the same session without committing or rolling back in the main transaction. PRAGMA (compiler directive) statement is used to define autonomous transaction in Oracle. The following is an Oracle autonomous transaction example.

Syntax to Define Autonomous Transaction in Oracle

PRAGMA AUTONOMOUS_TRANSACTION; /* in the declaration section of PL/SQL Block */

Oracle Stored Procedure Example for Autonomous Transaction

The following Oracle stored procedure for the autonomous transaction is to log the errors occurred in any PL/SQL program (Procedures, packages or functions, etc.). It will insert the error information into the table error_log and will commit the data without affecting any main transaction in any PL/SQL program. You can call this procedure from any PL/SQL program to log the error information. Below I will show you how. Create the following objects to test in your system:

Create Table Error_Log

CREATE TABLE error_log
(
   error_code          VARCHAR2 (100),
   error_msg           VARCHAR2 (4000),
   date_occurred       DATE,
   plsql_program_ref   VARCHAR2 (100)
)
/

Oracle Stored Procedure for Autonomous Transaction to Log Errors

Below procedure takes three parameters, which you need to pass at the time of calling the procedure from other stored procedures or functions at the time of the error.

CREATE OR REPLACE PROCEDURE prc_log_errors (v_error_code      IN VARCHAR2,
                                            v_error_msg       IN VARCHAR2,
                                            v_plsql_program   IN VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log (ERROR_CODE,
                          ERROR_MSG,
                          DATE_OCCURRED,
                          PLSQL_PROGRAM_REF)
       VALUES (v_error_code,
               v_error_msg,
               SYSDATE,
               v_plsql_program);

   COMMIT;
END;
/

Now you can call the stored procedure prc_log_errors from other PL/SQL program's exception handling section to log the error information. Here is an example:

Create Table test_data

CREATE TABLE test_data (some_data VARCHAR2 (100))
/

Create Function fnc_test

The following function will insert some data into the test_data table, and after that, it will generate the error because it is dividing by 0 in the next line. On error, in the exception section, it is calling the procedure prc_log_errors to log the error. If the function executes without error, then it will return TRUE else it will return FALSE. In the below case, it will return the FALSE after logging the error.

CREATE OR REPLACE FUNCTION fnc_test
   RETURN BOOLEAN
IS
   n   NUMBER;
BEGIN
   INSERT INTO test_data
       VALUES ('abc');

   /* generate error */
   n := 2 / 0;

   RETURN TRUE;
EXCEPTION
   WHEN OTHERS
   THEN
      prc_log_errors (TO_CHAR (SQLCODE), SQLERRM, 'FNC_TEST');
      RETURN FALSE;
END fnc_test;
/

Test

Call the above function fnc_test.

BEGIN
   IF fnc_test
   THEN
      COMMIT;
   ELSE
      ROLLBACK;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;
/

Even it is rolling back on fail, but still, the data will be saved in the error_log table, because the procedure prc_log_errors is using PRAGMA AUTONOMOUS_TRANSACTION.

Check the test_data table, should have no records.

SELECT * FROM test_data;

Output

no rows selected.

Check data in the error_log table

SELECT * FROM error_log;

Output

ERROR_CODE ERROR_MSG	                        DATE_OCCURRED	PLSQL_PROGRAM_REF
-1476      ORA-01476: divisor is equal to zero	27/03/2019 15:43:12	FNC_TEST

See also: