Log Table Creation

CREATE TABLE my_log_table 
( 
   code         INTEGER, 
   text         VARCHAR2 (4000), 
   created_on   DATE, 
   created_by   VARCHAR2 (100) 
)

A Dead Simple Logging Package

CREATE OR REPLACE PACKAGE my_logger 
IS 
   PROCEDURE putline (code_in IN INTEGER, text_in IN VARCHAR2); 
 
   PROCEDURE saveline (code_in IN INTEGER, text_in IN VARCHAR2); 
END;

The Log Package Body

CREATE OR REPLACE PACKAGE BODY my_logger 
IS 
   PROCEDURE putline (code_in IN INTEGER, text_in IN VARCHAR2) 
   IS 
   BEGIN 
      INSERT INTO my_log_table 
           VALUES (code_in, 
                   text_in, 
                   SYSDATE, 
                   USER); 
   END; 
 
   PROCEDURE saveline (code_in IN INTEGER, text_in IN VARCHAR2) 
   IS 
      PRAGMA AUTONOMOUS_TRANSACTION; 
   BEGIN 
      IF code_in = 0 
      THEN 
         /* No commit needed if no non-query DML executed */ 
         DBMS_OUTPUT.put_line ('Nothing to log!'); 
      ELSE 
         putline (code_in, text_in); 
         COMMIT; 
      END IF; 
   END; 
END;

It Works! :-)

DECLARE 
   l_count   PLS_INTEGER; 
BEGIN 
   DBMS_OUTPUT.put_line (SYSDATE); 
   my_logger.putline (1, 'Putline the date'); 
   my_logger.saveline (1, 'Saveline the date'); 
   my_logger.saveline ( 
   	   0, 'Nothing written to log, nothing to commit'); 
   ROLLBACK; 
 
   SELECT COUNT (*) INTO l_count FROM my_log_table; 
 
   DBMS_OUTPUT.put_line ('Left in log table = ' || l_count); 
END;

CREATE TABLE employees
AS
   SELECT * FROM hr.employees

Execute DDL Dynamically - Implicit Commit

CREATE OR REPLACE PROCEDURE non_auton_ddl 
   AUTHID CURRENT_USER 
IS 
BEGIN 
   EXECUTE IMMEDIATE 'create table temp_table1 (d date)'; 
END;

Execute DDL within Autonomous Transaction

CREATE OR REPLACE PROCEDURE auton_ddl 
   AUTHID CURRENT_USER 
IS 
   PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
   EXECUTE IMMEDIATE 'create table temp_table2 (d date)'; 
END;

Demonstration of Implicit Commit for Session

DECLARE 
   l_count PLS_INTEGER; 
BEGIN 
   DELETE FROM employees; 
 
   auton_ddl; 
 
   ROLLBACK; 
 
   SELECT COUNT (*) INTO l_count FROM employees; 
 
   DBMS_OUTPUT.put_line ( 
   	  'After autonomous DDL count = ' || l_count); 
END;

Scope of DDL Commit Limited

DECLARE 
   l_count PLS_INTEGER; 
BEGIN 
   DELETE FROM employees; 
 
   non_auton_ddl; 
 
   ROLLBACK; 
 
   SELECT COUNT (*) INTO l_count FROM employees; 
 
   DBMS_OUTPUT.put_line ( 
   	  'After non-autonomous DDL count = ' || l_count); 
END;