Oracle Associative arrays, how to define?

user_442806 0
Viewed 92

I am trying to create an associative array in Oracle, when I create a nested table, it works fine:

CREATE OR REPLACE TYPE TRNG_BOOK_AUTHORS_TAB 
AS TABLE OF VARCHAR2(500);

But when I add the index I get errors:

CREATE OR REPLACE TYPE TRNG_BOOK_AUTHORS_TAB 
AS TABLE OF VARCHAR2(500) INDEX BY PLS_INTEGER;
  • Error: PL/SQL: Compilation unit analysis terminated
  • Error(2,4): PLS-00355: use of pl/sql table not allowed in this context

What do I have wrong?

oracle-sqldeveloper associative-array oracle
1 Answers

You are creating a schema type; those can be nested tables or varying arrays (varrays), but not associative arrays.

From the documentation for the create type statement:

A standalone collection type that you create with the CREATE TYPE statement differs from a collection type that you define with the keyword TYPE in a PL/SQL block or package. For information about the latter, see "Collection Variable Declaration".

With the CREATE TYPE statement, you can create nested table and VARRAY types, but not associative arrays. In a PL/SQL block or package, you can define all three collection types.

user_2663040
Related