tracer.sql

Oracle PLSQL for DBAs 源代码

CREATE TABLE a_table
(COL1 NUMBER);

CREATE TYPE debug_o AS OBJECT ( string VARCHAR2(100) );
/
CREATE TYPE debug_t AS TABLE OF debug_o;
/

CREATE OR REPLACE FUNCTION tracer RETURN debug_t AS

  /*
    || Provide tracing information from as the return values
    || from this working function
  */

  PRAGMA AUTONOMOUS_TRANSACTION;
  v_trace debug_t := debug_t();

BEGIN

  v_trace.EXTEND;
  v_trace(v_trace.LAST) := debug_o('Started Insert At ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
  INSERT INTO a_table VALUES(1);
  COMMIT;

  v_trace.EXTEND;
  v_trace(v_trace.LAST) := debug_o('Completed Insert At ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));

  RETURN(v_trace);

END;
/
SELECT *
  FROM TABLE(tracer)
/