More

    Oracle Arrays – VARRAYs and Nested Table Arrays (AS TABLE OF)

    Oracle collections are database types that allow you to store sets of elements, similar to arrays and collections in Java. Storing data in collections is closer to the object oriented paradigm. 

    The data stored in an embedded collection may be accessed faster by the database than if stored in two separate tables.

     TYPES OF ORACLE COLLECTIONS

    Oracle database has 3 types of collections, which are varrays, nested tables and associative arrays.

    1. A varray is an ordered set of elements with index, similar to an array in Java.
      • Even if you only want to modify one element, you must supply all the elements for the varray.
      • A maximum size is set for the varray when creating it, and adding more elements will result in an exception; but you can change the size any time.
    2. A nested table is a table that is embedded within another table, and you can insert, update, and delete individual elements in a nested table.
      • A nested table array is a special case of a nested table with only one column.
    3. An associative array is a set of key and value pairs, similar to a HashMap in Java.
      • You can get value from the arrays using the key or index (position).

    Here, we will discuss varray and nested table array which are treated almost similarly, even by JDBC programs.

    CREATING, ALTERING AND DELETING A VARRAY TYPE

    You can create a varray type using CREATE TYPE statement and then use it to define a column or variable.

    You can create a varray type as:

    CREATE TYPE varray_type AS VARRAY(3) OF VARCHAR2(50);

    You can alter a varray type’s size using ALTER TYPE as:

    ALTER TYPE varray_type MODIFY LIMIT 5 CASCADE;

    NOTE: The CASCADE option propagates the change to any dependent objects in the database.

    You can drop a varray type as:

    DROP TYPE ARRAY_TABLE;

    Note: You cannot drop or replace a type with type or table dependents present.

    USING VARRAY TYPE IN A TABLE

    You can use the varray type to define a column in a table as:

    CREATE TABLE empvarray (

      emp_id INTEGER,

      emp_skills varray_type

    );

    INSERTING DATA INTO A TABLE WITH A VARRAY TYPE

    You can insert data to a table (e.g. empvarray) that contains a varray type (e.g. varray_type) as:

    insert into empvarray values (1, varray_type (‘java’, ‘sql’));

    GETTING INFORMATION ABOUT A VARRAY

    A table definition doesn’t tell you about the type of a varray.

    In SQL Plus, you can find the details about a varray using DESCRIBE keyword as:

    DESCRIBE varray_type;

    You can also do the same for a table in SQL Plus as:

    DESCRIBE empvarray;

    The system table user_varrays contains all user defined varrays and you can query it as:

    SELECT * FROM user_varrays

    Or

    SELECT parent_table_name, parent_table_column, type_name FROM user_varrays

    CREATING A NESTED TABLE ARRAY USING ‘AS TABLE OF <TYPE>’ SYNTAX

    Nested table array is a table with only one column.

    Nested table array is similar to a varray except that it doesn’t have an upper bound like varray.

    We can create a nested table array of one column type using the ‘AS TABLE OF’ syntax as:

    CREATE TYPE array_table AS TABLE OF VARCHAR2 (50);

    CREATE TYPE array_int AS TABLE OF NUMBER;       

    Note that here we don’t specify the size and hence there is no upper bound for a nested table array, unlike VARRAY.

    Altering and dropping

    You can alter or drop using ALTER and DROP commands respectively similar to VARRAY.

    You cannot drop or replace a type with type or table dependents

    USING NESTED TABLE ARRAY (AS TABLE OF) TYPE WITHIN A TABLE

    Unlike VARRAY type, we need to have an additional ‘NESTED TABLE … STORE AS …’ clause while creating tables with nested table arrays.

    CREATE TABLE empnested (

      emp_id INTEGER,

      emp_skills array_table

    )

    NESTED TABLE

    emp_skills

    STORE AS

    array_table1;

    array_table1 can be any name and there should not be any object/table with that name.

    Without the nested clause, you will get error as:

    [Error Code: 22913, SQL State: 99999]  ORA-22913: must specify table name for nested table column or attribute

    INSERTING DATA INTO A TABLE WITH A VARRAY TYPE

    INSERT INTO empnested values (1, array_table (‘java’, ‘sql’));

    Note that array_table is the array type and not the column name.

    RETRIEVING DATA FROM VARRAY AND NESTED TABLE ARRAY (AS TABLE OF)

    Simply trying to do a select * (e.g. Select * from empvarray) from a JDBC client like DBVisualizer will give null value for the VARRAY type/Nested table type. However you can see all entered values when you do a select * from statement in SqlPlus:

    EMP_ID

    ———-

    EMP_SKILLS

    ————————–

          1

    VARRAY_TYPE(‘java’, ‘sql’)

    You can find a sample program for inserting and retrieving arrays from stored procedures @ http://javajee.com/jdbc-program-to-retrieve-data-from-an-oracle-table-with-varray-and-nested-table-array-as-table-of

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox