Home arrow Oracle arrow Associative Arrays in Oracle PL/SQL: Introduction

Associative Arrays in Oracle PL/SQL: Introduction

In this series of articles, we shall concentrate on working with associative arrays in Oracle PL/SQL. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of much more advanced techniques using associative arrays with Oracle PL/SQL.

  1. Associative Arrays in Oracle PL/SQL: Introduction
  2. Associative Arrays in Oracle PL/SQL: a simple example
  3. Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop
  4. Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop
By: Jagadish Chatarji
Rating: starstarstarstarstar / 119
March 13, 2006

print this article



Consider this an add-on to my long PL/SQL series on DevShed.  If you are new to PL/SQL, I strongly suggest you to go through my huge “Database interaction with PL/SQL” series on this website.

All of the examples in this article have been tested only with Oracle 10g version 2.  I didn’t really test the examples in any of the previous Oracle versions.  I am confident that all of the examples should give no problems when worked with Oracle version 7.3 or above.  You can drop (or post) me a line if you run into trouble with any of them.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any change.

Associative Arrays in Oracle PL/SQL: the beginning

The “Associative Arrays” are also known as “Index-By” tables in PL/SQL.  We all know that a variable can hold only one value (as the variable occupies one memory location based on the data type chosen).  An “Associative Array” can also be considered a single variable, but with more than one memory location. 

An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.”  This “index” could simply be an integer or string, or something else. 

In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially).  Each pair of data would generally contain a “key” and a “value.”  If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.”  While storing information into an “Associative Array,” the user needs to specify both “key” and “value.” 

This concept looks very similar to the concept of arrays in C/C++.  In C/C++ an array is a continuous memory of several locations starting with zero as index.  But, here the “index” could be anything (there is no strict rule for working with the “index”).

A typical “Associative Array” in PL/SQL could be declared like this:


The above single statement contains a lot of meaning.  First of all, we are simply defining our own data type, “year_type.” Make sure that “year_type” is not a  variable.  It is a user defined data type.  According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”), organized with a BINARY_INTEGER index.  The BINARY_INTEGER in this scenario simply acts as a location number or position of the memory location within the table (or simply called an index).

The next section gives you a better understanding with a simple example.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: