Return a type : Function Return « Stored Procedure Function « Oracle PL / SQL

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>

Related examples in the same category

1.	Return varchar2 value from function
2.	Function return Integer
3.	Return value from a function
4.	Use function return value in select statement
5.	Save the returning value from a procedure to a variable
6.	RETURN statement.
7.	Multiple RETURN Statements
8.	Concatenates two strings into one:
9.	Return a varray from a function
10.	Append result from generator function to a table
11.	We use user function in DML statements
12.	This script demonstrates using a record type as a function return value
13.	Use function to check passwords
14.	Use user-defined function to combine and format columns