Mega Code Archive
Package Function with PRAGMA AUTONOMOUS_TRANSACTION
<![CDATA[
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>
]]>