auton_ddl.sql

REM Pass in -- for parameter to turn off autonomous transaction
REM Pass in /**/ to turn it on.

DROP TABLE temp_table;

CREATE OR REPLACE PROCEDURE execddl 
AUTHID CURRENT_USER
IS
   &1 PRAGMA AUTONOMOUS_TRANSACTION;                            
BEGIN
   EXECUTE IMMEDIATE 'create table temp_table (d date)';
END;
/

DROP TABLE emp_temp;

CREATE TABLE emp_temp AS SELECT * FROM emp;

SELECT COUNT (*)
  FROM emp_temp;

DELETE FROM emp_temp;

SELECT COUNT (*)
  FROM emp_temp;

EXEC execddl

ROLLBACK ;

SELECT COUNT (*)
  FROM emp_temp;



/*======================================================================
| 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/
*/