Accessing Table type/Record Type PLSQL function using Oracle SQL Statement.

SQL>
SQL>
SQL> create or replace type myRecordType as object
  2  ( seq int,
  3    a int,
  4    b varchar2(10),
  5    c date
  6  )
  7  /

Type created.

SQL>
SQL>
SQL> create or replace type myTableType
  2  as table of myRecordType
  3  /

Type created.

SQL>
SQL> create or replace function my_function return myTableType
  2  is
  3      l_data myTableType;
  4  begin
  5      l_data := myTableType();
  6
  7      for i in 1..5
  8      loop
  9          l_data.extend;
 10          l_data(i) := myRecordType( i, i, 'row ' || i, sysdate+i );
 11      end loop;
 12      return l_data;
 13  end;
 14  /

Function created.

SQL>
SQL> select *
  2    from TABLE ( cast( my_function() as mytableType ) )
  3   where c > sysdate+1
  4   order by seq desc
  5  /


       SEQ          A B          C
---------- ---------- ---------- ---------
         5          5 row 5      30-JUL-08
         4          4 row 4      29-JUL-08
         3          3 row 3      28-JUL-08
         2          2 row 2      27-JUL-08

SQL>
SQL> drop type myTableType;

Type dropped.

SQL> drop type myRecordType;

Type dropped.

SQL>
SQL>

Pragma Autonomous Transactions

AUTONOMOUS_TRANSACTION 

pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

You can apply this pragma to:

• Top-level (not nested) anonymous PL/SQL blocks
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers

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.

Example 6-43 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 6-44 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;
/

Example 6-45 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;
/

Example 6-46 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;
/

Example 6-47 Using Autonomous Triggers

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

-- create an autonomous trigger that inserts into the audit table before
-- each update of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
   BEFORE UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                :new.salary, :old.salary );
  COMMIT;
END;
/
-- update the salary of an employee, and then commit the insert
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 115;
COMMIT;

-- update another salary, then roll back the update
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 116;
ROLLBACK;

-- show that both committed and rolled-back updates add rows to audit table
SELECT * FROM emp_audit WHERE emp_audit_id = 115 OR emp_audit_id = 116;