pragma autonomous_transaction : restrict_references(<fuctionname>,wnds) and serially_resumable

pragma : serially_reusable

SQL> CREATE PACKAGE pkg1 IS

   PRAGMA SERIALLY_REUSABLE;
   num NUMBER := 0;
   PROCEDURE init_pkg_state(n NUMBER);
   PROCEDURE print_pkg_state;
END pkg1;
/  2    3    4    5    6    7


Package created.


SQL> CREATE PACKAGE BODY pkg1 IS
   PRAGMA SERIALLY_REUSABLE;
   PROCEDURE init_pkg_state (n NUMBER) IS
   BEGIN
      pkg1.num := n;
   END;
   PROCEDURE print_pkg_state IS
   BEGIN
      dbms_output.put_line('Num: ' || pkg1.num);
   END;
END pkg1;
/  2    3    4    5    6    7    8    9   10   11   12


Package body created.


SQL>
SQL>
SQL> exec pkg1.init_pkg_state(10);

PL/SQL procedure successfully completed.

SQL> exec pkg1.print_pkg_state;
Num: 0


PL/SQL procedure successfully completed.

pragma :autonomous_transaction

SQL> create table t(ab date);


Table created.


SQL> create or replace package pkg2 is
             function fn_auto return number;
           end pkg2;
/  2    3    4


Package created.


SQL> create or replace package body pkg2 is
            function fn_auto return number
             is
            begin
                insert into t values(sysdate);
                commit;
                return 1;
            end;
         end pkg2;
 /  2    3    4    5    6    7    8    9   10


Package body created.


SQL>  select pkg2.fn_auto() test from dual;
 select pkg2.fn_auto() test from dual
        *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "QDL.PKG2", line 5

SQL> create or replace package body pkg2 is
function fn_auto return number
is
pragma autonomous_transaction;
begin
insert into t values(systimestamp);
commit;
 return 1;
end;
end pkg2;
/  2    3    4    5    6    7    8    9   10   11


Package body created.

SQL>  select pkg2.fn_auto() test from dual;


      TEST
----------
         1

pragma :restrict_references(fn_auto,wnds)

make the compiler compile the function fn_auto according to WNDS (write no database ) , so after the package boday created, you see the compile error directly.