PL/SQL Package function returning VARRAY

PL/SQL Package function 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 Package Specification

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

• Function has no input parameters. so no need to specify ().

• Function return type must be type name, not variable name.

create or replace package pkgVARRY1
is
type varrayType is varray(11) of date;
function get_hiredate return varrayType;

end pkgVARRY1;

Create Package Body

• User can remove where condition , if row count knows in advance.
• exception will be thrown if select statement has more records than 11.

create or replace package body pkgVARRY1
is
function get_hiredate return varrayType
as
v_t varrayType;
begin
select hiredate bulk collect into v_t from emp where rownum<=11;
return v_t;
end;
end pkgVARRY1;

Consume Package in PL/SQL Anonymous Block

SQL>declare
v_array pkgVARRY1.varrayType; -- declare variable of type varrayType

-- Syntax:- variable name   packagename.typename.
begin
dbms_output.put_line('function returning varray of DATES');

--calling package function , no need to specify ()
v_array:=pkgVARRY1.get_hiredate;

--display using for loop.
for i in v_array.first..v_array.last
loop
dbms_output.put_line(v_array(i));
end loop;

SQL>Set serveroutput on;

OUTPUT:

SQL> /
function returning varray of DATES
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

PL/SQL procedure successfully completed.

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