USING VARRAY IN ORACLE

USING VARRAY IN ORACLE

                            VARRAY is an contiguous memory locations of any type in oracle PL/SQL. It is similar to Arrays in java/C#/javascript/php. Default indexed with integer,starting value is 1.

For ex:  List of Holidays falling under weekend days(sat,sun);

declare

day char(3);
--declare varray of type DATE 

TYPE varray_type IS VARRAY(5) OF DATE;
v2 varray_type;

begin

--initialize sample Dates here.
v2:=varray_type(
to_date('2013-01-12','yyyy-MM-DD'),
to_date('2013-03-05','yyyy-MM-DD'),
to_date('2013-05-13','yyyy-MM-DD'),
to_date('2013-06-12','yyyy-MM-DD'),
to_date('2013-12-25','yyyy-MM-DD')
); -- Up to 5 DATES
--VARRAY FIRST METHOD VARRAY LAST METHOD

for j in V2.FIRST..V2.COUNT
loop
day := to_char(v2(j),'DY');
dbms_output.put_line(v2(j)||'    '||day);

--Check day is Saturday or Sunday using IF statement

if(day='SAT' or day='SUN') then
   dbms_output.put_line(v2(j)||'  is  Weekend '||day);
end if;     

end loop;
exception
--Any exception will be handled here with complete error msg 

when others then
       dbms_output.put_line('exception occured'||sqlerrm);
end;

OUTPUT:

SQL> /
12-JAN-13    SAT
12-JAN-13  is  Weekend SAT
05-MAR-13    TUE
13-MAY-13    MON
12-JUN-13    WED
25-DEC-13    WED

PL/SQL procedure successfully completed.

Tags:USING VARRAY IN ORACLE,oracle pl/sql VARRAY,Arrays in PL/SQL,PL/SQL IF STATEMENT, PL/SQL VARRAY OF DATE,oracle VARRAY FIRST element, oracle VARRAY LAST ELEMENT, oracle VARRAY COUNT,oracle VARRAY LIMIT.