Wednesday, January 13, 2010

PL/SQL Function to Return Table Type

As i described in my previous post PL/SQL Procedure to return Table Type. In this post i m goin to show the same thing but with database Function to return multiple rows.

--Create Database Type as Object

create or replace type TypeForFunction
(EmpCd number,
EmpName Varchar2(60),
DepartCode number,
Salary number );

--Create table of the Type

Create or replace type TableForFunction as table of TypeForFunction; 

--Create Database Function

Create or replace
(deptCode number) RETURN TableForFunction
OutTableParam TableForFunction;
select cast(
Multiset(select empno, ename, deptno, sal
from scott.emp
where deptno = deptcode)
as TableForFunction)
into outtableparam
from dual;

return OutTableParam;

--To return values from this function

SQL> select * from table(MyFunction(10));

Output would be like

EMPCD                 EMPNAME          DEPARTCODE      SALARY                
---------------------- ------------------------ ---------------------- ----------------------
7782                   CLARK                       10                     2450                  
7839                   KING                          10                     5000                  
7934                   MILLER                     10                     1300             


  1. can you show this solution wrapped in a package? I think it will work for me, but I'm getting compile errors on the object type when defining the header. New to pl/sql and I have a query that I need to run in a 3rd party app. I want to be able to do a simple select from the package in the app to display multiple rows. Thanks!