Learning notes for Oracle database autonomous transactions

Oracle autonomous transactions are stored procedures and functions that can handle internal transactions themselves and are not affected by external transactions and are declared with pragma autonomous_transaction. To create an autonomous transaction, you must use the Pragma autonomous_transaction statement in Pl/sql in the top-level of the anonymous block or in the definition section of a stored procedure, function, packet, or trigger.
Here are some scenarios for using autonomous transactions:

(1) Declaring autonomous Function in 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;
/
(2) declaring autonomous Standalone Procedure

CREATE OR REPLACE 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;
/
(3) declaring autonomous Pl/sql block

DECLARE
PRAGMA autonomous_transaction;
emp_id Number (6): = 200;
Amount Number (6, 2): = 200;
BEGIN
UPDATE Employees
SET salary = Salary-amount
WHERE employee_id = emp_id;

COMMIT;
End;
/
(4) declaring autonomous in Trigger

CREATE OR REPLACE TRIGGER log_sal
  before UPDATE of salary on EMP
  for each ROW
DECLARE
  PRAGMA Autonomous_transaction;
BEGIN
  INSERT into log
    (log_id, Up_date, New_sal, old_sal)
  VALUES
&NB sp;   (: old.employee_id, Sysdate,: new.salary,: old.salary);
  COMMIT;
End;
Autonomous transactions can be nested, and the nesting depth is constrained only by the Init.ora parameter transactions (simultaneous transaction count, which defaults to 1.1 times times sessions). Because the autonomous transaction is separate from the primary transaction (MT), it cannot detect the current state of the modified row. It is as if they have been in separate sessions until the main transaction is committed, and they are not available for autonomous transactions. However, the reverse is different: The master transaction can detect the results of an autonomous transaction that has already been performed.
If at attempts to access a resource that is controlled by MT, a deadlock may occur, package cannot be declared at, only the function and procedure owned by package can be declared at. The master transaction and the autonomous transaction are completely different transactions, so the lock cannot be shared. The end of an autonomous transaction must commit a commit, rollback, or execute DDL, or it will produce an Oracle error Ora-06519:active autonomous transaction detected and rolled back. The savepoint cannot be rolled back to a savepoint in the parent transaction in an autonomous transaction and can only be used internally to save points.
errors that may be encountered:
ora-06519– Check to active autonomous transaction, rollback--no commit, rollback, or DDL operation when exiting autonomous transaction
ora-14450– attempting to access the transaction-level temporary table being used
ora-00060– Check for deadlock

while waiting for resource