return resultset from stored procedure oracle 11g

Return resultset from stored procedure oracle 11g

Returning a Result set from a function in a Package.

1) Create/Declare a PL/SQL collection.

2) Create Type of PL/SQL collection.

3) Create function which returns Above type.

Here is the example.

I am using scott schema from  Oracle DB.

Creating  a Collection in PL/SQL is 2 step process.

1.

//Declaring a type etype is a table of Dates

  type etype is table of date  index by binary_integer;
 

2.

// Declaring a type

v_emp etype;

Now create a Function.  function specification should be like this.

3.

function <function name>  return  <pl/sql collection>;

ex:

function getEmp_hiredates return etype;

Complete example.

1) Create a package  specification

   which has 

                   1.PL/SQL collection type

                   2.Function returns collection.

create or replace package pkg1
as
  type etype is table of date
  index by binary_integer;
v_emp etype;
function getEmp_hiredates return etype;
end pkg1;

SQL>/

Package created.

2)  Create  a  package body  with function returning PL/SQL collection.

create or replace package body pkg1
as

function getEmp_hiredates return etype
as
begin
select hiredate bulk collect into v_emp from emp;
return v_emp; end getEmp_hiredates;

end pkg1;

SQL>  /

Package body created.

3)  Executing  Package function (which returns PL/SQL table or collection).

begin
for i in pkg1.getEmp_hiredates().first..pkg1.getEmp_hiredates().last
loop

--accessing table elements from a function
dbms_output.put_line(pkg1.getEmp_hiredates()(i));
end loop;
end;

SQL> set serveroutput on;
SQL> /
23-JAN-82
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82

PL/SQL procedure successfully completed.


Note: The above process can be repeated to multiple  in a table.
because we are returning table means it can accommodate   multiple.

Example 2:  Return all emp  records using function table

Create a package which as Collection Type and a function which returns collection

1.Package Specification

create or replace package pkg1
as
  type etype is table of emp%rowtype
  index by binary_integer;
v_emp etype;
function getEmp_records return etype;
end pkg1;

2.Package Body

create or replace package body pkg1
as
function getEmp_records return etype
as
begin
select * bulk collect into v_emp from emp;
return v_emp; end getEmp_records;
end pkg1;

Calling/Executing Package function which returns PL/SQL Table.

begin
for i in pkg1.getEmp_records().first..pkg1.getEmp_records().last
loop
--accessing table elements from a function
dbms_output.put_line(pkg1.getEmp_records()(i).empno||' '||pkg1.getEmp_records()(i).ename);
end loop;
end;

SQL> /
9003 peter
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

PL/SQL procedure successfully completed.


Tags:return resultset from stored procedure oracle 11g,return PL/SQL table from function oracle 11g, return table from function oracle 11g, How to return table from oracle function,return collection type from function oracle 11g.