VARRAY

VARRAY:

  • Varray sands for variable-size array.
  • Varray has a fixed limit on its size,specified as part of the declaration.
  • Varray index always starts with 1 not 0.
  • A varray is automatically NULL when it is declared and must be initialized before its elements can be referenced.

SYNTAX:

      TYPE < type_name > IS  VARRAY  (size_limit) OF  <data_type  > [NOT NULL];

       < varray_name >    type_name;  ----declaring variable.

examples:

1) 
DECLARE   
     TYPE char_type IS VARRAY (5) OF VARCHAR2 (10); 
         --calls the Varray constructor   
     c_list  char_type := char_type (); 
BEGIN  
--allocates space in the varray   
   c_list.EXTEND;
   c_list (1) := 'SCOTT'; 
   c_list.EXTEND;
   c_list (2) := 'TIGER';   
       --print the varray elements by using its methods FIRST and LAST   
   FOR i IN c_list.FIRST.. c_list.LAST   
   LOOP 
   DBMS_OUTPUT.put_line (c_list (i));    
   END LOOP; 
END;
/
output:
SCOTT
TIGER
---------------------------------------------------------------------------
Example 2):

DECLARE   
          --define a varray of emp type      
    TYPE myarray IS VARRAY (20) OF emp%ROWTYPE; 
      --declare and initialize a null set of rows
    v_list    myarray := myarray ();
    CURSOR cu_emp  IS  SELECT *  FROM emp;
    v_index   NUMBER;
BEGIN    v_index := 1;    
    FOR i IN cu_emp LOOP
    v_list.EXTEND;
    SELECT * INTO v_list (v_index) FROM emp WHERE empno = i.empno;          
  DBMS_OUTPUT.put_line ('empno = '|| v_list (v_index).empno|| ' ename ='|| v_list (v_index).ename);
        --increment the index value       
    v_index := v_index+ 1;    
    END LOOP; 
END;
/
OUTPUT:
empno = 7369 ename =SMITH
empno = 7499 ename =ALLEN
empno = 7521 ename =WARD
empno = 7566 ename =JONES
empno = 7654 ename =MARTIN
empno = 7698 ename =BLAKE
empno = 7782 ename =CLARK
empno = 7788 ename =SCOTT
empno = 7839 ename =KING
empno = 7844 ename =TURNER
empno = 7876 ename =ADAMS
empno = 7900 ename =JAMES
empno = 7902 ename =FORD
empno = 7934 ename =MILLER

PL/SQL procedure successfully completed.
----------------------------------------------------------------------
3) Common Exceptions in Varrays:

DECLARE
  TYPE char_type IS VARRAY (5) OF VARCHAR2 (10);
      --we declare a varray but not initialize so system will raise an error
  c_list   char_type;
BEGIN
    -- allocates space in the varray
   c_list.EXTEND;
   c_list (1):= 'SCOTT';
   c_list.EXTEND;
   c_list (2) := 'TIGER';
        --print the varray elements by using its methods FIRST and LAST
   FOR i IN c_list.FIRST.. c_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (c_list (i));
   END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 7

NOTE:
       This exception raises when you forgot to initialize the varray. So once after declaration of the varray variable and then initialize the variable is must otherwise system will rise above exception.
------------------------------------------------------------------------------------
Example 4:
DECLARE
   TYPE char_type IS VARRAY (3) OF VARCHAR2 (10);
   c_list   char_type := char_type ();
BEGIN
   c_list.EXTEND;
   c_list (1):= 'SCOTT';
   c_list.EXTEND;
   c_list (2):= 'TIGER';
      --before allocates space in the varray we try to use that variable then system will raise an error
   c_list (3):= 'KING';
       --print the varray elements by using its methods FIRST and LAST
   FOR i IN c_list.FIRST.. c_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (c_list (i));
   END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 10

NOTE:
     The exception means that subscript 3 is unavailable. It does not exist.While you defined the varray as three elements in size, you allocate the space for only two elements.Therefore the variable has only two valid subscripts, one and two.
-----------------------------------------------------------------------------------------------
Example 5):

DECLARE
   TYPE char_type IS VARRAY (3) OF VARCHAR2 (10);
   c_list   char_type := char_type ();
BEGIN
   c_list.EXTEND;
   c_list (1):= 'SCOTT';
   c_list.EXTEND;
     c_list (2) := 'TIGER';
   c_list.EXTEND;
     c_list (3) := 'KING';
   c_list.EXTEND;
      --print the varray elements by using its methods FIRST and LAST
   FOR i IN c_list.FIRST.. c_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (c_list (i));
   END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 11

NOTE:
       In the above program we declare the varray size is 3 then we try to allocate the space for the 4th element then system will raise an error.
------------------------------------------------------------------------------------------
NOTE:
    You cannot use the DELETE method with a varray to remove its elements. 
Unlike PL/SQL tables, varrays are dense, and using the DELETE method causes an error.

As shown in the following example:

DECLARE
TYPE varray_type IS VARRAY(3) OF CHAR(1);
varray varray_type := varray_type('A','B','C');
BEGIN
varray.DELETE(3);
END;
/
varray.DELETE(3);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored.

                                                                                              Gouthami.
-----------------------------------------------------------------------------------------------------------------

 

Comments

Post a Comment

Popular posts from this blog

PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE : We want to maintain state of the global variable we are using pragma serially_reusable in packages. ------------with out serially reusable pragma in packages SQL> create or replace package pkg3            is                   g number(3):=27;            end pkg3;           / Package created. SQL> begin                pkg3.g :=30;           end;          / PL/SQL procedure successfully completed. SQL> begin                  dbms_output.put_line(pkg3.g);           end;          / 30 PL/SQL procedure successfully completed. -------------------------------------------- serially reusable in packages SQL> create...
Read more

COLLECTION METHODS

Collection methods: A collection method is a built-in function or procedure that operates on collections and is called using dot notation. Following are the functions used with collections. EXISTS COUNT LIMIT FIRST AND LAST PRIOR AND NEXT EXTEND TRIM DELETE Syntax: collection_name.method_name [(parameters)] Collection methods cannot be called from SQL statements. Only the EXISTS method can be used on a NULL collection. All other methods applied on a null collection raise the COLLECTION_IS_NULL error. 1.COUNT: COUNT method returns the number of elements with the space allocated in Varrays and Nested tables. The COUNT method displays number of elements in associate arrays. COUNT can be smaller than LIMIT for Varrays. Example: DECLARE --Index by table type    TYPE etab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;    emptab  etab; --Nested Table Type    TYPE tab_type IS TABLE OF VARCHAR2 (10);    t...
Read more

CURSOR PARAMETERS

CURSOR WITH  PARAMETERS: A cursor can be declare with parameters. Note: cursor parameters can be assigned default values. the scope of the cursor  parameters is local to the cursor the mode of the  parameters can only be IN. Example: Declare   Cursor c1 (p_deptno  emp.deptno%type) is   select ename,sal,deptno   from emp   where deptno=p_deptno; begin     for i in c1 (10) loop     dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);     end loop;         for i in c1(20) loop         dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);         end loop;     dbms_output.put_line('hai'); end; /
Read more