PRAGMA AUTONOMOUS_TRANSACTION:
Transaction flow in autonomous transaction explained in the below picture:
Defining Autonomous Transactions:
This pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. For the purposes of the
autonomous transaction, a PL/SQL block can be any of the following:
- Top-level (but not nested) anonymous PL/SQL blocks
- Functions and procedures, defined either in a package or as standalone programs
- Methods (functions and procedures) of an object type
- Database triggers.
About It:
- Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction.
- You can log events, increment retry counters, and so on, even if the main transaction rolls back.
- Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK,and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.
- Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits.The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default).
- If you set the isolation level of the
main transaction to SERIALIZABLE, changes made by its autonomous
transactions are not visible
to the main transaction when it resumes. (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;).
- If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
Note:
- Transaction properties set in the main transaction apply only to that transaction, not to its autonomous transactions, and vice versa.
- Cursor attributes are not affected by autonomous transactions.
Declaring an Autonomous Function in a Package:
CREATE OR REPLACE PACKAGE emp_actions AS — package specification
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS — package body
— code for function raise_salary
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
new_sal NUMBER(8,2);
BEGIN
UPDATE employees SET salary = salary + sal_raise WHERE employee_id = emp_id;
COMMIT;
SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
RETURN new_sal;
END raise_salary;
END emp_actions;
/
----------------------------------------------------------------------------------------------------------------
Example:2
CREATE OR REPLACE PACKAGE BODY ds_rms_volume_reprocess_pkg
AS
PROCEDURE log_message (p_order_number IN VARCHAR2,
p_receipt_number IN VARCHAR2,
p_chr_module IN VARCHAR2,
p_chr_proc_name IN VARCHAR2,
p_chr_msg_code IN VARCHAR2,
p_chr_msg_text IN VARCHAR2,
p_chr_user_name IN VARCHAR2,
p_num_audsid IN NUMBER,
p_chr_log_loc IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
INSERT INTO pub.md_rms_debug_log
VALUES (pub.md_rms_debug_log_s.NEXTVAL,
SYSDATE,
p_order_number,
p_receipt_number,
p_chr_module,
p_chr_proc_name,
p_chr_msg_code,
p_chr_msg_text,
p_chr_user_name,
p_num_audsid,
p_chr_log_loc);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
COMMIT;
END;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END log_message;
PROCEDURE cs_receipt_notes_update (in_chr_user IN VARCHAR2,
in_batch_id IN NUMBER,
in_app_name IN VARCHAR2,
out_chr_err_code OUT VARCHAR2,
out_chr_err_buff OUT VARCHAR2)
IS
v_receipt_status VARCHAR2 (100);
v_receipt_id NUMBER;
v_error_msg VARCHAR2 (2000);
v_err_code NUMBER;
v_record_count NUMBER := 0;
v_chr_subject VARCHAR2 (100);
v_source_system_code VARCHAR2 (100) := 'VOLUME_REPROCESS_PKG';
v_ds_id VARCHAR2 (20) := NULL;
v_customer_id NUMBER := 0;
l_chr_user VARCHAR2 (20) := in_chr_user;
BEGIN
out_chr_err_code := '0';
out_chr_err_buff := 'SUCCESS';
v_chr_subject := 'RcptNotesUpdValidation';
v_source_system_code := 'VOLUME_REPROCESS_PKG';
EXCEPTION
WHEN OTHERS
THEN
out_chr_err_code := '1';
out_chr_err_buff := 'Exception :' || SUBSTR (SQLERRM, 1, 200);
log_message (p_order_number => NULL,
p_receipt_number => NULL,
p_chr_module => 'ds_rms_volume_reprocess_pkg',
p_chr_proc_name => 'cs_receipt_notes_update',
p_chr_msg_code => '1',
p_chr_msg_text => out_chr_err_buff,
p_chr_user_name => UPPER (in_chr_user),
p_num_audsid => USERENV ('sessionid'),
p_chr_log_loc => 'notes_update_1');
END cs_receipt_notes_update;
END ds_rms_volume_reprocess_pkg;
/
-------------------------------------------------------------------------------------------------------------------
Declaring an Autonomous Standalone Procedure:
CREATE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE employees SET salary = salary – amount WHERE employee_id = emp_id;
COMMIT;
END lower_salary;
/
Declaring an Autonomous PL/SQL Block:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id NUMBER(6);
amount NUMBER(6,2);
BEGIN
emp_id := 200;
amount := 200;
UPDATE employees SET salary = salary – amount WHERE employee_id = emp_id;
COMMIT;
END;
/
Declaring an Autonomous Trigger:
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
— bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;
END;
/
When to Use Autonomous Transactions:
- Logging mechanism
- Perform commits and rollbacks in your database triggers
- Reusable application components
- Avoid mutating table trigger errors for queries
- Call user-defined functions in SQL that modify tables
- Retry counter
No comments:
Post a Comment