Oracle PL/SQL for DBAs

Tracing

Most PL/SQL tracing tools (SQL Trace , DBMS_TRACE, etc.) require you to run an operation and then look elsewhere for the trace output. Even Oracle’s DBMS_OUTPUT package (the simplest debug tool of all) requires separate output when using a development tool like Toad or PL/SQL Developer .

Table functions allow debug information to be included within query results. When combined with autonomous transactions, they can even provide tracing for DML operations. Consider the following function.

    /* File on web: tracer.sql */
    CREATE OR REPLACE FUNCTION tracer
                      RETURN debug_t AS
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_debug debug_t := debug_t(  );
    BEGIN
      v_trace.EXTEND;
      v_trace(v_debug.LAST) := 'Started Insert At ' ||
                                TO_CHAR(SYSDATE,'HH24:MI:SS');
      INSERT INTO a_table VALUES(1);
      COMMIT;
      v_trace.EXTEND;
      v_trace(v_debug.LAST) := 'Completed Insert At ' ||
                                TO_CHAR(SYSDATE,'HH24:MI:SS');
      RETURN(v_trace);
    END;

Without the AUTONOMOUS TRANSACTION clause , I would get the error ORA-14551: cannot perform a DML operation inside a query when executing the query. With this clause in place, I can run the function using a SELECT statement.

    SQL> SELECT *
      2    FROM a_table;

    no rows selected

    SQL> SELECT *
      2    FROM TABLE(debug);

    COLUMN_VALUE
    ----------------------------
    Started Insert At 22:04:28
    Completed Insert At 22:04:28

    SQL> SELECT *
      2    FROM a_table;

          COL1
    ----------
             1