Package Function with 'PRAGMA AUTONOMOUS_TRANSACTION' : Package Body « Function Procedure Packages « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> CREATE TABLE stuff_to_fix
  2  (stuff VARCHAR2(1000),
  3   fixed VARCHAR2(1));

Table created.

SQL>
SQL> CREATE OR REPLACE PACKAGE fixer AS
  2
  3    PROCEDURE fix_stuff;
  4    PROCEDURE fix_this ( p_thing_to_fix VARCHAR2 );
  5
  6  END fixer;
  7  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY fixer AS
  2
  3    PROCEDURE fix_this ( p_thing_to_fix VARCHAR2 ) IS
  4      PRAGMA AUTONOMOUS_TRANSACTION;
  5    BEGIN
  6      INSERT INTO stuff_to_fix(stuff,fixed)VALUES(p_thing_to_fix,'N');
  7      COMMIT;
  8    END fix_this;
  9
 10    PROCEDURE fix_stuff IS
 11      CURSOR curs_get_stuff_to_fix IS
 12      SELECT stuff,ROWID  FROM stuff_to_fix WHERE fixed = 'N';
 13
 14    BEGIN
 15
 16      FOR v_stuff_rec IN curs_get_stuff_to_fix LOOP
 17
 18        EXECUTE IMMEDIATE v_stuff_rec.stuff;
 19
 20        UPDATE stuff_to_fix SET fixed = 'Y' WHERE ROWID = v_stuff_rec.rowid;
 21
 22      END LOOP;
 23
 24      COMMIT;
 25
 26    END fix_stuff;
 27
 28  END fixer;
 29  /

Package body created.

SQL>
SQL> DROP TABLE stuff_to_fix;

Table dropped.

SQL>








27.11.Package Body
27.11.1.Package with only one function
27.11.2.Package with two procedures
27.11.3.Package declaration and body
27.11.4.Use of 'get' and 'set' prefixes
27.11.5.Use package method in a procedure
27.11.6.Call function in a Package
27.11.7.Method overload
27.11.8.Package method overloading
27.11.9.Reference method from another package
27.11.10.Package Function with 'PRAGMA AUTONOMOUS_TRANSACTION'
27.11.11.Create a package containing stored procedure DELETE_ORDERS and stored function GET_employee_NAME.
27.11.12.Package initialization.
27.11.13.Use package member variable to pass value