Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Varrays in PL/SQL


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

A VARRAY is similar to a nested table except an upper bound must be specified in the declaration.  Like nested tables, they can be stored in the database. But unlike nested tables, individual elements cannot be deleted so they remain dense.  The following code shows how
the previous example can be rewritten to use a VARRAY.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE t_collection IS VARRAY(5) OF NUMBER(10);

  l_coll  t_collection;
  l_idx   NUMBER;
BEGIN
  -- Initialise the collection with two values.
  l_coll := t_collection(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    l_coll.extend;
    l_coll(l_coll.last) := i;
  END LOOP load_loop; 

  -- Can't delete from a VARRAY.
  -- l_coll.DELETE(3);

  -- Traverse collection
  l_idx := l_coll.FIRST;
  << display_loop >>
  WHILE l_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
    l_idx := l_coll.NEXT(l_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

Extending the load_loop to "3 .. 6" attempts to extend the VARRAY beyond its limit of 5 elements resulting in the following error:

DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 13
 

=====================================================================================

The keyword VARRAY is expanded as VARYING ARRAY, which also acts as a replacement keyword for it in the type structure creation. This type was introduced in Oracle 8 for storing a fixed-size sequential collection of elements of the same type. The VARRAY type is densely populated, meaning that it cannot have any gaps between its elements unlike the associative array or the nested table type. It is also bounded, meaning that there is a predefined upper limit on the number of elements stored in this type. The upper bound value once defined during the type definition cannot be modified throughout its lifespan.

 

This type is similar to the arrays in the other programming languages like C and Java, but the index value in Oracle starts with 1 for this type and not 0, unlike the other languages. When we try to assign an element to the 0th index value, we can expect to get the ORA-06532: Subscript outside of limit exception.

 

The VARRAYs are mainly used in an environment where the number of elements to be stored/processed are already known and its size is very stable. Similar to the nested table types, the VARRAYs can also be created over a scalar data type like a number, varchar2, date or composite data types like records, objects, and another nested table or VARRAY type.

 

The prototype for declaring a VARRAY type transiently in a PL/SQL unit is shown below,

 

TYPE <Type_name>

IS

  [VARRAY | VARYING ARRAY] (<Bound_limit>) OF <Scalar_datatype | Composite_datatype>;

 

The prototype for creating a VARRAY type persistently in the database is shown below,

 

CREATE OR REPLACE TYPE <Type_name>

IS

  [VARRAY | VARYING ARRAY] (<Bound_limit>) OF <Scalar_datatype | Composite_datatype>;

 

In the below example, a VARRAY type is declared with its upper bound as 5 but when we try to allocate space for 6 elements using the extend method, we get an ORA-06532: Subscript outside of limit exception as shown below,

 

DECLARE

type type_varray IS varray(5) OF NUMBER;

l_vt_var1 type_varray:=type_varray();

BEGIN

  l_vt_var1.extend(6);

END;

/

 

Error report –

 

ORA-06532: Subscript outside of limit

ORA-06512: at line 5

06532. 00000 -  "Subscript outside of limit"

*Cause:    A subscript was greater than the limit of a varray

           or non-positive for a varray or nested table.

*Action:   Check the program logic and increase the varray limit

           if necessary.

 

In the above error report, the action tag suggests us not to allocate more than the upper limit or to increase the upper bound limit of the VARRAY to resolve this issue.

 

The below block describes the basic functioning of the VARRAY type having an upper bound limit of 5 with a record type of two attributes as its element type. The first 5 employee IDs and names are bulk fetched into the VARRAY’s instance and are looped successfully for the display.

 

DECLARE

type type_rec

IS

  record

  (

    employee_id   NUMBER,

    employee_name VARCHAR2(100));

type type_varray IS varray(5) OF type_rec;

l_vt_var1 type_varray;

BEGIN

  SELECT

    employee_id,

    last_name

    ||', '

    ||first_name AS employee_name bulk collect

  INTO

    l_vt_var1

  FROM

    employees

  FETCH

    FIRST 5 rows only;

  FOR loop_vt IN l_vt_var1.first..l_vt_var1.last

  LOOP

    dbms_output.put_line(l_vt_var1(loop_vt).employee_id||': '||l_vt_var1(

    loop_vt).employee_name);

  END LOOP loop_vt;

END;

/

 

Result:

 

100: King, Steven

101: Williams, Neena

102: De Haan, Lex

103: Hunold, Alexander

104: Ernst, Bruce

 

In the below listing, a VARRAY type with 3 as its upper bound of VARCHAR2(1) as its element type is declared and assigned with A, B, C as its index values respectively. When the delete method is used for deleting the first element of the VARRAY type, the unit fails with a PLS-00306: wrong number or types of arguments in call to 'DELETE' exception.

 

DECLARE

type type_varray IS varray(3) OF VARCHAR2(1);

l_vt_var1 type_varray:=type_varray();

BEGIN

  l_vt_var1.extend(3);

  l_vt_var1(1):='A';

  l_vt_var1(2):='B';

  l_vt_var1(3):='C';

  l_vt_var1.delete(1);

END;

/

 

Error report –

 

ORA-06550: line 9, column 3:

PLS-00306: wrong number or types of arguments in call to 'DELETE'

ORA-06550: line 9, column 3:

PL/SQL: Statement ignored

 

When the same block is executed again, but this time with no parameters for the delete method, the unit is successfully completed. The delete method without any parameters deletes all the available elements in a VARRAY type. Thus, VARRAY type allows us to delete all the elements at once, but not individually.

 

DECLARE

type type_varray IS varray(3) OF VARCHAR2(1);

l_vt_var1 type_varray:=type_varray();

BEGIN

  l_vt_var1.extend(3);

  l_vt_var1(1):='A';

  l_vt_var1(2):='B';

  l_vt_var1(3):='C';

  l_vt_var1.delete;

END;

/

 

PL/SQL procedure successfully completed.

 

Similar to the in indices of clause, we have the in values of clause, which allows the values of one collection type to be used as the index pointers of another collection type. In the below example, nested table type of PLS_INTEGER datatype is declared and a VARRAY type of VARCHAR2 data type with an upper bound of 10 is declared.

 

% Note: The between condition is not supported by the in values of clause, unlike the in indices of clause

 

 

In our below snippet, the nested table type is used in the in values of clause. Thus, the element values of the nested table type 5, 6, and 7 acts as the index values of the VARRAY type and processes the alphabets E, F, and G in the DML statement.

 

DECLARE

type type_ntt

IS

  TABLE OF pls_integer;

type type_vt IS varray(10) OF VARCHAR2(1);

l_ntt_var1 type_ntt:=type_ntt();

l_vt_var2 type_vt  :=type_vt();

BEGIN

  l_ntt_var1.extend(3);

  l_ntt_var1(1):=5;

  l_ntt_var1(2):=6;

  l_ntt_var1(3):=7;

  l_vt_var2.extend(10);

  l_vt_var2(1) :='A';

  l_vt_var2(2) :='B';

  l_vt_var2(3) :='C';

  l_vt_var2(4) :='D';

  l_vt_var2(5) :='E';

  l_vt_var2(6) :='F';

  l_vt_var2(7) :='G';

  l_vt_var2(8) :='H';

  l_vt_var2(9) :='I';

  l_vt_var2(10):='J';

  FORALL loop_value IN VALUES OF l_ntt_var1

  INSERT INTO tbl_varray VALUES (l_vt_var2(loop_value));

END;

/

 

When we query the table TBL_VARRAY, it produces the below results.

 

Result:

 

E

F

G

 

In the below scripting, a VARRAY type is created over an object type having two attributes and has an upper bound value of 6.

 

CREATE OR REPLACE type type_obj

IS

  object

  (

    l_obj_var1 CHAR,

    l_obj_var2 VARCHAR2(50));

  /

CREATE OR REPLACE type type_vt IS varray(6) OF type_obj;

/

 

The below anonymous block creates an instance of the persistent VARRAY type TYPE_VT and is assigned with six element values. The VARRAY is then transformed into a virtual heap table with the help of the TABLE function. The VARRAY type elements are then displayed using a cursor FOR loop where the VARRAY type is mimicked as a traditional heap table with the help of the TABLE function as shown below,

 

DECLARE

  l_vt_var1 type_vt;

BEGIN

  l_vt_var1:=type_vt(type_obj('A', 'Attributes'),

                     type_obj('B', 'Bind Variables'),

                     type_obj('C', 'Context Switching'),

                     type_obj('D', 'Data Type'),

                     type_obj('E', 'Execute Immediate'),

                     type_obj('F', 'Forward Declaration'));

  FOR loop_vt IN

  (

    SELECT

      *

    FROM

      TABLE(l_vt_var1)

  )

  LOOP

    dbms_output.put_line(loop_vt.l_obj_var1||' for '||loop_vt.l_obj_var2);

  END LOOP loop_vt;

END;

/

 

Result:

 

A for Attributes

B for Bind Variables

C for Context Switching

D for Data Type

E for Execute Immediate

F for Forward Declaration

 

We normally use the implicit cursor attribute SQL%ROWCOUNT to find the number of rows affected during the previous DML operation. But, when dealing with a bulk operation, this attribute may not be of much help as we need the number of rows affected for each index value. The SQL%BULK_ROWCOUNT attribute serves this process by providing granular information about the number of rows affected during each iteration of the FORALL DML operation.

 

A VARRAY type with its upper bound as 6 is created over an object type with two attributes pertaining to the employee’s identifier, and salary is created in the below snippet as shown below.

 

CREATE OR REPLACE type type_obj

IS

  object

  (

    employee_id NUMBER,

    salary      NUMBER);

  /

CREATE OR REPLACE type type_vt IS varray(6) OF type_obj;

/

 

In the below block of code, an instance of the above-created VARRAY type is created and is assigned with 6 employees ID and salary information. This list is then taken for a 10% increment using the FORALL statement, provided that the ID and the salary match for an employee. After the increment is performed, the SQL%ROWCOUNT attribute is then looped for the VARRAY type’s index values to show the number of rows updated for the index values individually.

 

DECLARE

  l_vt_var1 type_vt:=type_vt();

BEGIN

  l_vt_var1.extend(6);

  l_vt_var1(1):=type_obj(101,20400);

  l_vt_var1(2):=type_obj(105,5760);

  l_vt_var1(3):=type_obj(109,10900);

  l_vt_var1(4):=type_obj(110,9840);

  l_vt_var1(5):=type_obj(112,8500);

  l_vt_var1(6):=type_obj(113,8280);

  FORALL loop_vt IN indices OF l_vt_var1

  UPDATE

    employees

  SET

    salary=l_vt_var1(loop_vt).salary+(l_vt_var1(loop_vt).salary*10)/100

  WHERE

    employee_id=l_vt_var1(loop_vt).employee_id

  AND salary   =l_vt_var1(loop_vt).salary;

  FOR loop_vt IN l_vt_var1.first..l_vt_var1.last

  LOOP

    dbms_output.put_line('Index ['||loop_vt||'] has updated '||

    sql%bulk_rowcount(loop_vt)||' row(s).');

  END LOOP loop_vt;

END;

/

 

Result:

 

Index [1] has updated 1 row(s)

Index [2] has updated 1 row(s)

Index [3] has updated 0 row(s)

Index [4] has updated 1 row(s)

Index [5] has updated 0 row(s)

Index [6] has updated 1 row(s)

VARRAY Type Enhancement in 12c

The restriction of using the TABLE function only on the persistent VARRAY types has been revoked from and after the Oracle version 12c. The TABLE function can be used on the transient VARRAY types with a restriction of having them declared in the package specification and not elsewhere.

 

In the below code snippet, the package PKG_VT is created with a VARRAY type of a record data type with two attributes. The upper bound value of the VARRAY type is defined as 6. The package also sports a procedure PROC_VT for explaining the TABLE function used over the transient VARRAY type. The procedure creates an instance of the VARRAY type and allocates the spaces for the 6 elements. The instance variable is then assigned with some random car related element values. The TABLE function works perfectly on this transient VARRAY type and gives it a traditional heap table look inside the cursor FOR loop and prints the stored element values as shown in the below code.

 

CREATE OR REPLACE PACKAGE pkg_vt

IS

type type_rec

IS

  record

  (

    car_brand VARCHAR2(50),

    car_model VARCHAR2(50));

type type_vt IS varray(6) OF type_rec;

PROCEDURE proc_vt;

END;

/

CREATE OR REPLACE PACKAGE body pkg_vt

IS

  PROCEDURE proc_vt

  IS

    l_vt_var1 type_vt:=type_vt();

  BEGIN

  l_vt_var1.extend(6);

    l_vt_var1(1).car_brand:='Aston Martin';

    l_vt_var1(1).car_model:='One-77';

    l_vt_var1(2).car_brand:='Audi';

    l_vt_var1(2).car_model:='R8';

    l_vt_var1(3).car_brand:='Bugatti';

    l_vt_var1(3).car_model:='Veyron';

    l_vt_var1(4).car_brand:='Chevrolet';

    l_vt_var1(4).car_model:='Camaro';

    l_vt_var1(5).car_brand:='Jaguar';

    l_vt_var1(5).car_model:='XJ220';

    l_vt_var1(6).car_brand:='Tesla';

    l_vt_var1(6).car_model:='Model X';

    for loop_vt in (select * from table(l_vt_var1))loop

    dbms_output.put_line('Car Brand: '||loop_vt.car_brand||', Car Model: '||loop_vt.car_model);

    end loop loop_vt;

  END;

END;

/

 

The procedure PROC_VT is then executed using the below statement, printing out the stored element values.

 

EXEC pkg_vt.proc_vt;

 

Result:

 

Car Brand: Aston Martin, Car Model: One-77

Car Brand: Audi, Car Model: R8

Car Brand: Bugatti, Car Model: Veyron

Car Brand: Chevrolet, Car Model: Camaro

Car Brand: Jaguar, Car Model: XJ220

Car Brand: Tesla, Car Model: Model X

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational