Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Tuesday, 14 March 2017

PL/SQL Varray


     Varray is an user defined data type, which is used to store same datatype in a single unit, it is also same as nested table i.e., before we are storing values. We must initialize the values through constructor, and also using varrays we are storing up to 2 GB data. Varrays also supports all collection methods as same a nested table and also supports limit collection method. This collection method returns size of the array. This is a user defined type, so we are creating in two step process.
    
         Syntax:-  type typename is varray(maxsize) of data type(size);
                           Variablename typename:=typename();

DECLARE
   TYPE t1 IS VARRAY (10) OF VARCHAR2 (10);

   V_t   t1
            := t1 ('a',
                   'b',
                   'c',
                   'd');
   Z     BOOLEAN;
BEGIN
   DBMS_OUTPUT.put_line (v_t.LIMIT);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   DBMS_OUTPUT.put_line (v_t.FIRST);
   DBMS_OUTPUT.put_line (v_t.LAST);
   DBMS_OUTPUT.put_line (v_t.PRIOR (3));
   DBMS_OUTPUT.put_line (v_t.NEXT (3));
   DBMS_OUTPUT.put_line (v_t.LAST);

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   z := v_t.EXISTS (3);

   IF z = TRUE
   THEN
      DBMS_OUTPUT.
       put_line ('inde z exists with an element' || ' ' || v_t (3));
   ELSE
      DBMS_OUTPUT.put_line ('index z does not exists');
   END IF;

   v_t.EXTEND;
   DBMS_OUTPUT.put_line (v_t.COUNT);
   v_t (5) := 'e';
   v_t.EXTEND (2);
   v_t (6) := 'f';
   v_t (7) := 'g';

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   v_t.EXTEND (3, 4);

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   v_t.TRIM (4);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   v_t.delete;
   DBMS_OUTPUT.put_line (v_t.COUNT);
END;


Note:- in varrays we are not allowed to delete range of elements, but we can delete all the elements using delete collection method


You May Like:

PLSQL Record


Share this article :

1 comments:

  1. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete