PL/SQL Package procedure returning VARRAY

PL/SQL Package procedure returning VARRAY

VARRAYs are used to if user knows record count in advance. i.e fixed size Data structures in PL/SQL.

It can contain array of single column or array of record Types.

Here is the Package which has function which returns array of Hiredates.

Create a Package Specification

• Here i specified varray size as 11  , u can specify any number.

• Procedure has one output parameters.

 

create or replace package pkgVARRY1
is
type varrayType is varray(11) of date;
procedure sp_get_hiredate(v_hireArray out varrayType );

end pkgVARRY1;

Create a Package Body

 

create or replace package body pkgVARRY1
is
procedure sp_get_hiredate(v_hireArray out varrayType )
as
begin
select hiredate bulk collect into v_hireArray from where  emp rownum<=11;
end;
end pkgVARRY1;

Create a PL/SQL anonymous block to consume above package function

 

declare

v_array pkg1.varrayType;

begin

dbms_output.put_line('Package procedure returning VARRAY');

pkg1.sp_get_hiredate(v_array);

for i in v_array.first..v_array.last

loop

dbms_output.put_line(v_array(i));

end loop;

end;

Tags:PL/SQL Package Procedure returning VARRAY,PL/SQL VARRAYS, how to declare varrays in PL/SQL package. How to return VARRAY from a procedure in PL/SQL,