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
