Exforsys

H I D E

Home arrow Technical Training arrow Oracle 11g Tutorial

Table of Contents

 Oracle 11g Collections
 Oracle 11g Collections - Nested Tables
 Oracle 11g Collections - VARRAY
 Oracle 11g Collections - Collection Methods

Oracle 11g Collections - VARRAY

Page 3 of 4
Author: Saurabh Gupta     Published on: 2nd Jun 2011

Oracle 11g Collections

VARRAY

VARRAYs are enhanced version of nested tables, which were introduced in Oracle 8i. They contain limited and defined number of densely populated elements, whose index is sequential and numeric. The minimum bound of the index is 1, current bound is the total number of resident elements and maximum bound is the varray size. At any moment, current bound cannot exceed the maximum bound.

In terms of handling and usage, they are similar to nested tables in major situations. VARRAYs can exist as schema objects, referenced from PL/SQL blocks, and can be stored in database tables.

Unlike nested tables, which are always stored as separate storage table, varrays exist as single object in the same table. Only in exceptional cases when size of varray exceeds 4KB, it is relocated outside the table but in the same tablespace.

Syntax [3a]

In SQL

Sample Code
  1. CREATE  [OR  REPLACE]  TYPE  type_name  IS   {VARRAY  |  VARYING  ARRAY}  (size_limit) OF  element_type  
Copyright exforsys.com


Syntax [3b]

In PL/SQL

Sample Code
  1. DECLARE
  2. TYPE  type_name  IS  {VARRAY  |  VARYING  ARRAY}  (size_limit) OF  element_type  [NOT  NULL];
Copyright exforsys.com


Size_limit represents the maximum count of elements in the array. Being a persistent form of collection, varrays require initialization.

Element_type can be one of the data type available till Oracle 11g.

VARRAYS in PL/SQL

Example [6a]

VARRAYs can be locally declared in a PL/SQL block and can be referenced within the scope of the block.

Sample Code
  1. DECLARE
  2.    TYPE EX_VRR_TAB IS VARRAY(5) OF VARCHAR2(100);
  3.    L_BATSMAN EX_VRR_TAB := EX_VRR_TAB();
  4.    L_WICKET EX_VRR_TAB := EX_VRR_TAB();
  5.    L_BOWLER EX_VRR_TAB := EX_VRR_TAB();
  6. BEGIN
  7.    L_BATSMAN := EX_VRR_TAB(‘Sachin’,’Sehwag’,’Gambhir’,’Kohli’,’Yuvraj’,);
  8.    L_WICKET := EX_VRR_TAB(‘Dhoni’,);
  9.    L_BOWLER := EX_VRR_TAB(‘Bhajji’,’Nehra’,’Zaheer’,’Praveen’,’Munaf’);
  10. END;
Copyright exforsys.com


VARRAYs in SQL

Example [6b]

The example below shows the creation of a VARRAY as schema object

Sample Code
  1. SQL> CREATE OR REPLACE TYPE NUM_VARRAY AS VARRAY(10) OF INTEGER;
  2. /
  3. Type created.
Copyright exforsys.com


Example [6c]

The VARRAY created in the schema can be referenced in a PL/SQL block

Sample Code
  1. DECLARE
  2.    L_INTEGER NUM_VARRAY := NUM_VARRAY();
  3. BEGIN
  4.    FOR I IN 1..5
  5.    LOOP
  6.         L_INTEGER.EXTEND;
  7.       L_INTEGER(I) := i*2;
  8.    END LOOP;
  9. END;
Copyright exforsys.com


Ads

Example [6d]

The VARRAY created can be used as a column type. The example demonstrates the Insert and Update process.

Sample Code
  1. SQL> CREATE OR REPLACE TYPE SALES_TYPE AS OBJECT ( SALES_DATE DATE,
  2.          SALES_QTY NUMBER);
  3.  
  4. Type created.
  5.  
  6. SQL> CREATE OR REPLACE TYPE SALES_VARRAY AS VARRAY(10) OF SALES_TYPE;
  7.  
  8. Type created.
  9.  
  10. SQL> CREATE TABLE ORDERS (ORD_ID NUMBER,
  11.                           ITEM_CODE VARCHAR2(100),
  12.                           ITEM_SALES SALES_VARRAY);
  13.  
  14. TABLE created.  
  15.  
  16. SQL> INSERT INTO ORDERS (SEQ_ORD.NEXTVAL,
  17.                                 ‘ELEC’,
  18.                                  SALES_VARRAY(
  19.                                                   SALES_TYPE(SYSDATE,130),
  20.                                                   SALES_TYPE(SYSDATE-5,57),
  21.                                                   SALES_TYPE(SYSDATE-10,130)
  22.                                                  )
  23.                                 );
  24.  
  25. 1 row inserted.
  26.  
  27. SQL>DECLARE
  28.       L_SALES SALES_VARRAY ;
  29.     BEGIN
  30.        SELECT ITEM_SALES
  31.        INTO L_SALES
  32.        FROM ORDERS
  33.        WHERE ORD_ID=100
  34.        FOR UPDATE OF ITEM_SALES;
  35.          L_SALES(2).SALES_QTY := 140 ; -- update the second element
  36.        UPDATE ORDERS
  37.          SET ITEM_SALES = L_SALES
  38.          WHERE ORD_ID=100;
  39.    END ;
  40.    /
  41.  
  42. PL/SQL block successfully completed.
  43.  
  44. SQL> COMMIT;
  45.  
  46. Commit complete.
Copyright exforsys.com


Read Next: Oracle Functions and Procedures


 
This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

  1. Oracle 11g Result Cache
  2. Compound Triggers in Oracle 11g
  3. Oracle 11g Invisible Index
  4. Oracle 11g Virtual Columns
  5. Oracle 11g Collections
  6. Oracle Functions and Procedures
  7. Oracle 11g Exception Handling
  8. Oracle 11g Read Only Tables
  9. Oracle 11g Subprogram Overloading
  10. Conditional compilation in Oracle PL/SQL
  11. Oracle Pragma
  12. UTL_FILE
  13. Manage Oracle dependencies
  14. Oracle 11g SecureFiles
  15. Oracle VPD implementation
  16. PL/Scope and PL/SQL Hierarchical Profiler
  17. Oracle Partitioning Overview
  18. Oracle Analytic Enhancements
  19. Oracle XML Storage
 

Comments

 

Ads

Exforsys Connect

Ads

Exforsys e-Newsletter

ebook