CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperassociative-array
Sam Carleton
Sam Carleton

Reputation: 1398

Oracle Associative arrays, how to define?

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;

What do I have wrong?

Upvotes: 3

Views: 524

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 3

Related Questions