autonserial.sql

DROP TABLE emp2;
CREATE TABLE emp2 AS SELECT * FROM emp;

CREATE OR REPLACE PROCEDURE fire_em_all
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   DELETE FROM emp2;

   COMMIT;
EXCEPTION -- London 2/18/2002: cover errors!
   WHEN OTHERS
   THEN
      log81.saveline (SQLCODE, 'error in fire_em_all');
      ROLLBACK;
END;
/

DECLARE
   num   INTEGER;
BEGIN
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

   SELECT COUNT (*)
     INTO num
     FROM emp2;

   DBMS_OUTPUT.put_line ('Before isolated AT delete ' || num);
   fire_em_all;

   SELECT COUNT (*)
     INTO num
     FROM emp2;

   DBMS_OUTPUT.put_line ('After isolated AT delete ' || num);
   COMMIT; -- ROLLBACK;

   SELECT COUNT (*)
     INTO num
     FROM emp2;

   DBMS_OUTPUT.put_line ('After MT commit ' || num);
END;
/



/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc. 
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/