Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Associative Array

PL/SQL Associative Array

Summary: in this tutorial, you will learn about Oracle PL/SQL associative arrays including declaring arrays, populating values, and iterating over their elements.

Introduction to Oracle PL/SQL associative arrays

Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements.

First, an associative array is single-dimensional. It means that an associative array has a single column of data in each row, which is similar to a one-dimension array.

Second, an associative array is unbounded, meaning that it has a predetermined limits number of elements.

Third, an associative array is sparse because its elements are not sequential. In other words, an associative array may have gaps between elements.

Finally, an associative array has elements which have the same data type, or we call them homogenous elements.

Note that associative arrays were known as PL/SQL tables in Oracle 7, and index-by tables in Oracle 8 and 8i. Their names were changed to associative arrays in Oracle 9i release 1.

An associative array can be indexed by numbers or characters.

Declaring an associative array is a two-step process. First, you declare an associative array type. And then, you declare an associative array variable of that type.

Declaring an associative array type

The following shows the syntax for declaring an associative array type:

TYPE associative_array_type IS TABLE OF datatype [NOT NULL] INDEX BY index_type;

In this syntax:

  • The associative_array_type is the name of the associative array type.
  • The datatype is the data type of the elements in the array.
  • The index_type is the data type of the index used to organize the elements in the array.
  • Optionally, you can specify NOT NULL to force every element in the array must have a value.

The following example declares an associative array of characters indexed by characters:

TYPE t_capital_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);

Declaring an associative array variable

After having the associative array type, you need to declare an associative array variable of that type by using this syntax:

associative_array associative_array_type

For example, this statement declares an associative array t_capital with the type t_capital_type:

t_capital t_capital_type;

Accessing associative array elements

To access an array element, you use this syntax:

array_name(index)

Note that index can be a number or a character string.

Assigning associative array elements

To assign a value to an associative array element, you use the assignment operation (:=):

array_name(index) := value;

Oracle PL/SQL associative array example

The following anonymous block shows how to declare an associative array and assigns values to its elements:

DECLARE -- declare an associative array type TYPE t_capital_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50); -- declare a variable of the t_capital_type t_capital t_capital_type; BEGIN t_capital('USA') := 'Washington, D.C.'; t_capital('United Kingdom') := 'London'; t_capital('Japan') := 'Tokyo'; END; /

Associative array methods

Associative arrays have a number of useful methods for accessing array element index and manipulating elements effectively.

To call a method you use the following syntax:

array_name.method_name(parameters);

This syntax is similar to the syntax of calling a method in C# and Java.

In this tutorial, we introduce you to two useful methods called FIRST and NEXT(n).

The method FIRST returns the first index of the array. If an array is empty, the FIRST method returns NULL.

The method NEXT(n) returns the index that succeeds the index n. If n has no successor, then the NEXT(n) returns NULL.

The FIRST and NEXT(n) methods are useful in iterating over the elements of an array using a WHILE loop:

l_index := array_name.FIRST; WHILE l_index IS NOT NULL LOOP -- access the array element -- array_name(l_index) l_index := array_name.NEXT(l_index); END LOOP;

Putting it all together

The following anonymous block illustrates how to declare an associative array, populate its elements, and iterate over the array elements:

DECLARE -- declare an associative array type TYPE t_capital_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50); -- declare a variable of the t_capital_type t_capital t_capital_type; -- local variable l_country VARCHAR2(50); BEGIN t_capital('USA') := 'Washington, D.C.'; t_capital('United Kingdom') := 'London'; t_capital('Japan') := 'Tokyo'; l_country := t_capital.FIRST; WHILE l_country IS NOT NULL LOOP dbms_output.put_line('The capital of ' || l_country || ' is ' || t_capital(l_country)); l_country := t_capital.NEXT(l_country); END LOOP; END; /

Here is the output:

The capital of Japan is Tokyo The capital of USA is Washington, D.C. The capital of United Kingdom is London

In this tutorial, you have learned about Oracle PL/SQL associative arrays including declaring arrays, populating values, and iterating over their elements.

  • Was this tutorial helpful?
  • YesNo
Previous Mutating Table Error in Oracle
Next PL/SQL Nested Tables

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.