1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Collection in oracle – varray

Discussion in 'SQL PL/SQL' started by TechQueryPond, Apr 5, 2016.

  1. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA


    Collection in Oracle – Varray

    Varray

    1)Varray provide feature of data structure in Oracle.
    2)It can store a fixed-size sequential collection of elements of the same type.
    3)This is used to store the ordered collection of data.

    4)According to its structure the varray consist of contiguous memory locations.
    5)The address is start from lowest to highest.
    6)It start to store element from lowest address to highest address.

    7)The starting index for varray is 1.
    8)This is an one-dimensional array.


    Code (SQL):


    DECLARE
       CURSOR cur_employee IS
       SELECT  NAME FROM employee;
       TYPE emp_list IS varray (10) OF employee.name%TYPE;
      empname_list emp_list := emp_list();
       mycount INTEGER :=0;
    BEGIN
       FOR i IN cur_employee LOOP
          mycount := mycount + 1;
          empname_list.extend;
          empname_list(mycount)  := i.name;
          dbms_output.put_line('Employee Nane ->>>     '||empname_list(mycount));
       END LOOP;
    END;
    /