1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. 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!

VARRAY in PL/SQL

Discussion in 'SQL PL/SQL' started by SBH, Dec 23, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    VARRAY is the third form of collection available in Oracle, which stands for “Variable sized Arrays”. It is ordered set of fixed number of homogeneous elements, which is available in both SQL and PL/SQL. They can be physically stored in database as schema objects.
    Size of varray must be associated with the type definition. Prior to 10gR2 version, the upper bound could not be altered. But it can be adjusted in later version of Oracle. VARRAYs find their optimum application when data set, which has to be stored in order and is relatively small.

    2. Notes

    • The index key must be positive limit in the range of 1 to 2147483647
    • In SQL, unlike Nested Tables, it is not stored at different segment location. It is stored in the relational format in the same table segment.
    • The order of elements is preserved in the database
    • In SQL, VARRAY can be used to define a column in relational table
    • In PL/SQL, VARRAY must be initialized using empty constructor before its use. Note than empty collection is different from NULL collection.
    • VARRAY data storage orientation is dense. Data elements cannot be deleted from between, but can only be trimmed from the end

    3. Syntax

    In PL/SQL

    Code (Text):
    DECLARE
    TYPE  type_name  IS  {VARRAY  |  VARYING  ARRAY}  (size_limit) OF  element_type  [NOT  NULL];
    In the above syntax, size_limit represents the maximum count of elements in the array. It must be positive.

    In SQL

    Code (Text):
    CREATE  [OR  REPLACE]  TYPE  type_name  IS   {VARRAY  |  VARYING  ARRAY}  (size_limit) OF  element_type  
    4. Using VARRAY in SQL

    A varray TYPE_ORDERS is created in database, which can accommodate string elements. Below SQL creates the TYPE_ORDERS type.

    Code (SQL):
    CREATE TYPE TYPE_ORDERS IS VARRAY(10) OF VARCHAR2(100);
    /
    TYPE created.
    A Table ORDERS is created which contains a column ORDER_NAME of above varray type.

    Code (SQL):
    CREATE TABLE ORDERS (
     ORDER_ID NUMBER,
     ORDER_NAME  TYPE_ORDERS)
    /

    TABLE created.
    Test data is inserted as below

    Code (SQL):
    INSERT INTO ORDERS VALUES (1, TYPE_ORDERS('Order 1','Order 2','Order 3'))
    /
    INSERT INTO ORDERS VALUES (2, TYPE_ORDERS('Order 21','Order 22','Order 23'))
    /
    Querying the ORDERS table; output shows the varray column as a collection.

    Code (SQL):
    SQL> SELECT * FROM ORDERS;

      ORDER_ID ORDER_NAME
    ---------- --------------------------------------------------
             1 TYPE_ORDERS('Order 1', 'Order 2', 'Order 3')
             2 TYPE_ORDERS('Order 21', 'Order 22', 'Order 23')
    Below SQL uses TABLE function to display the ORDERS table data in relational format.

    Code (SQL):
    SELECT T1.ORDER_ID, T2.*
    FROM ORDERS T1, TABLE(T1.ORDER_NAME) T2
    /

      ORDER_ID COLUMN_VALUE
    ---------- ---------------
             1 ORDER 1
             1 ORDER 2
             1 ORDER 3
             2 ORDER 21
             2 ORDER 22
             2 ORDER 23
    5. Updating a VARRAY element

    Since VARRAY is ordered set of dense elements, an element cannot be updated in SQL, but it can be achieved in PL/SQL. In SQL, complete VARRAY must can be updated as below.

    Code (SQL):
    UPDATE ORDERS
    SET ORDER_NAME = TYPE_ORDERS('ORDER 100','ORDER 200','ORDER 300')
    WHERE ORDER_ID=2
    /

    1 ROW updated.
    Check the above update in the ORDERS table

    Code (SQL):
    SQL> SELECT * FROM ORDERS;

      ORDER_ID ORDER_NAME
    ---------- --------------------------------------------------
             1 TYPE_ORDERS('Order 1', 'Order 2', 'Order 3')
             2 TYPE_ORDERS('ORDER 100', 'ORDER 200', 'ORDER 300')
    If a single element has to be updated, it has to be done through PL/SQL block. Illustration is as below.

    Code (SQL):
    DECLARE
      L_VARRAY TYPE_ORDERS;
    BEGIN
      SELECT ORDER_NAME
      INTO L_VARRAY
      FROM ORDERS
      WHERE ORDER_ID=2;
     
      L_VARRAY(1) := 'Updated Order 1';
     
      UPDATE ORDERS
      SET ORDER_NAME = L_VARRAY
      WHERE ORDER_ID=2;
    END;
    /

    PL/SQL PROCEDURE successfully completed.
    Check the above update in the ORDERS table

    Code (SQL):
    SQL> SELECT * FROM ORDERS;

      ORDER_ID ORDER_NAME
    ---------- --------------------------------------------------
             1 TYPE_ORDERS('Order 1', 'Order 2', 'Order 3')
             2 TYPE_ORDERS('Updated Order 1', 'ORDER 200', 'ORDER 300')
    6. VARRAY collection type methods

    [TABLE]Method Description
    EXISTS(n) Returns TRUE if the nth element in a PL/SQL table exists
    COUNT Returns the number of elements that a PL/SQL table currently contains
    LIMIT Returns maximum number of elements a varray can hold.
    FIRST | LAST Returns the first and last (smallest and largest) index number in a PL/SQL table. Returns NULL , if it is empty
    PRIOR (n) Returns the index number that precedes index in a PL/SQL table
    NEXT (n) Returns the index number that succeeds index in a PL/SQL table
    EXTEND EXTEND appends one null element to a collection. EXTEND (n) appends n null elements to a collection. EXTEND (n, j) appends n copies of the jth element to a collection
    TRIM TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection.
    DELETE DELETE removes all elements from the table. DELETE (n) removes n elements from the table. DELETE (m,n) removes all elements in range from m..n from the table[/TABLE]

    Syntax for using a collection method:
    Code (Text):
    collection_name.method_name[(parameters)]
    Restrictions
    1. Collection methods are not callable from SQL statements.
    2. No method can be used with a NULL collection except EXISTS. Oracle raises COLLECTION_IS_NULL exception if any method is used with NULL collection.

    7. Examples

    The PL/SQL block below declares a varray CLUB_VAR, initializes it and displays the maximum size and count of varray. Note that difference in output of LIMIT and COUNT methods.

    Code (SQL):
    DECLARE
     TYPE  CLUB_VAR IS  VARRAY(100)  OF  NUMBER  ;
     DTAB    CLUB_VAR :=  CLUB_VAR(100,200)  ;
    BEGIN
     DBMS_OUTPUT.PUT_LINE(  'VARRAY size: '  ||  DTAB.LIMIT  )  ;
     DBMS_OUTPUT.PUT_LINE(  'VARRAY count: '  ||  DTAB.COUNT)  ;
    END;
    /

    VARRAY SIZE: 100
    VARRAY COUNT: 2

    PL/SQL PROCEDURE successfully completed.
    The PL/SQL block below demonstrates the restricted use of DELETE method with Varrays.

    Code (SQL):
    DECLARE
      TYPE  CLUB_VAR IS  VARRAY(100)  OF  NUMBER  ;
      DTAB    CLUB_VAR :=  CLUB_VAR(100,200,300,400,500,600,700)  ;
    BEGIN
      DTAB.DELETE(4);
    END;

    SQL> /
     DTAB.DELETE(4);
     *
    ERROR at line 5:
    ORA-06550: line 5, COLUMN 2:
    PLS-00306: wrong NUMBER OR types OF arguments IN CALL TO 'DELETE'
    ORA-06550: line 5, COLUMN 2:
    PL/SQL: Statement ignored
    Instead, varray can be trimmed from the end to remove the unwanted elements. Below PL/SQL block removes last four elements of the varray CLUB_VAR.

    Code (SQL):
    DECLARE
     TYPE  CLUB_VAR IS  VARRAY(100)  OF  NUMBER  ;
     DTAB    CLUB_VAR :=  CLUB_VAR(100,200,300,400,500,600,700)  ;
    BEGIN
     DTAB.TRIM(4);
     FOR I IN  1..DTAB.COUNT
     LOOP
     DBMS_OUTPUT.PUT_LINE(DTAB(I));
     END LOOP;
    END;
    /

    100
    200
    300

    PL/SQL PROCEDURE successfully completed.