plsql table type with index of is complaining

Question

Pl/SQL: Intent: My intent was to access employee tuple object defied as cursor below by using key as the employee_id.

Problem: I created a cursor - *l_employees_cur* and want to create type table as below type *l_employees_t*, as below but the compiler is complaining saying that PLS-00315 implementation restriction unsupported table index type.

CURSOR l_employees_cur
  IS
    SELECT employee_id,manager_id,first_name,last_name FROM employees;
type l_employees_t
IS
  TABLE OF l_employees_cur%rowtype INDEX BY employees.employee_id%TYPE;

The definition of employees.employee_id is:

EMPLOYEE_ID    NUMBER(6) NOT NULL

why can't I do this ? or Am I doint something wrong.

Answer 1

From the Oracle Documenation:

Associative Arrays

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).

The data type of index can be either a string type or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

Answer 2

I think that your mistake is the declaration of the plsql table.

Why don't you try the next one:

type l_employees_t
IS
  TABLE OF l_employees_cur%rowtype INDEX BY pls_integer;

I also have a question for you: What is the meaning of EMPLOYEE_ID NOT NULL NUMBER(6) in your code above?

Greetings Carlos