Mega Code Archive
Return a type
SQL>
SQL> CREATE TABLE a_table(COL1 NUMBER);
Table created.
SQL>
SQL> CREATE or replace TYPE debug_o AS OBJECT ( string VARCHAR2(100) );
2 /
SQL> CREATE or replace TYPE debug_t AS TABLE OF debug_o;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION tracer RETURN debug_t AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 v_trace debug_t := debug_t();
4 BEGIN
5 v_trace.EXTEND;
6 v_trace(v_trace.LAST) := debug_o('Started Insert At ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
7 INSERT INTO a_table VALUES(1);
8 COMMIT;
9 v_trace.EXTEND;
10 v_trace(v_trace.LAST) := debug_o('Completed Insert At ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
11 RETURN(v_trace);
12 END;
13 /
Function created.
SQL>
SQL> SELECT * FROM TABLE(tracer)
2 /
STRING
----------------------------------------------------------------------------------------------------
Started Insert At 19:30:39
Completed Insert At 19:30:39
SQL>
SQL> drop table a_table;
Table dropped.
SQL>