2-14. Executing One Transaction from Within Another
Problem
You are executing a transaction, and you are faced with the need to suspend your current work, issue a completely separate transaction, and then pick up your current work. For example, say you want to log entries into a log table. The log entries should be persisted separately from the current transaction such that if the transaction fails or is rolled back, the log entries will still be completed.
Solution
Start an autonomous transaction to make the log entry. This will ensure that the log entry is performed separately from the current transaction. In the following example, an employee is deleted from the EMPLOYEES table. Hence, a job is ended, and the job history must be recorded into the JOB_HISTORY table. In the case that something fails within the transaction, the log entry into the JOB_HISTORY table must be intact. This log entry cannot be rolled back because it is performed using an autonomous transaction.
The code to encapsulate the autonomous transaction needs to be placed into a named block that can be called when the logging needs to be performed. The following piece of code creates a PL/SQL procedure that performs the log entry using an autonomous transaction. (You will learn more about procedures in Chapter 4.) Specifically notice the declaration of PRAGMA AUTONOMOUS_TRANSACTION. That pragma specifies that the procedure executes as a separate transaction, independent of any calling transaction.
CREATE OR REPLACE PROCEDURE log_job_history (emp_id IN
employees.employee_id%TYPE,
Job_id IN jobs.job_id%TYPE,
Department_id IN employees.department_id%TYPE,
employee_start IN DATE) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO job_history
VALUES (emp_id,
employee_start,
sysdate,
job_id,
department_id);
COMMIT;
END;
The LOG_JOB_HISTORY procedure inserts an entry into the log table separately from the transaction that is taking place in the calling code block. The following code performs the job termination, and it calls the log_substitution procedure to record the history:
DECLARE
CURSOR dept_removal_cur IS
SELECT *
FROM employees
WHERE department_id = 10
FOR UPDATE;
dept_removal_rec dept_removal_cur%ROWTYPE;
BEGIN
-- Delete all employees from the database who reside in department 10
FOR dept_removal_rec IN dept_removal_cur LOOP
DBMS_OUTPUT.PUT_LINE('DELETING RECORD NOW'),
DELETE FROM employees
WHERE CURRENT OF dept_removal_cur;
-- Log the termination
log_job_history(dept_removal_rec.employee_id,
dept_removal_rec.job_id,
dept_removal_rec.department_id,
dept_removal_rec.hire_date);
END LOOP;
DBMS_OUTPUT.PUT_LINE('The transaction has been successfully completed.'),
EXCEPTION
-- Handles all errors
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('The transaction has been rolled back due to errors, please try again.'),
ROLLBACK;
END;
If this code block is executed and then rolled back, the entry into the job history table remains, because it is performed as a separate, autonomous transaction.
How It Works
An autonomous transaction is a transaction that is called by another transaction and that runs separately from the calling transaction. Autonomous transactions commit or roll back without affecting the calling transaction. They also have the full functionality of regular transactions; they merely run separately from the main transaction. They allow parallel activity to occur. Even if the main transaction fails or is rolled back, the autonomous transaction can be committed or rolled back independently of any other transactions in progress.
An autonomous transaction must be created with a top-level code block, trigger, procedure, function, or stand-alone named piece of code. In the solution, you saw that a procedure was created to run as an autonomous transaction. That is because it is not possible to create an autonomous transaction within a nested code block. To name a transaction as autonomous, you must place the statement PRAGMA AUTONOMOUS_TRANSACTION within the declaration section of a block encompassing the transaction. To end the transaction, perform a COMMIT or ROLLBACK.