Calling Oracle Associative Array from UPDATE subquery gives PLS-00201 error-CodePudding

I am trying to lookup values from an associative array to update a table, but am getting error. Here is a bare minimum code to demonstrate the issue (my actual query is much more complex).

Am I missing something, or if this is an Oracle limitation is there a way around it?

CREATE TABLE t_employee AS SELECT cast('john' as varchar2(30)) emp_name, CAST (NULL AS NUMBER (5)) emp_id FROM DUAL;
DECLARE
    TYPE emp_typ IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
    emp_lookup   emp_typ;
BEGIN
    emp_lookup ('john') := 1234;
    UPDATE t_employee e
       SET emp_id = (SELECT emp_lookup (e.emp_name) FROM DUAL);
END;
/

--DROP TABLE t_employee;

> PLS-00201: identifier 'E.EMP_NAME' must be declared

and when I change the update to a simpler construct:

UPDATE t_employee SET emp_id = emp_lookup (t_employee.emp_name);

I get this weirdness: PLS-00382: expression is of wrong type

Same with SELECT. It seems to me that we cannot use associative arrays directly in SQL statements.

CodePudding user response：

An associative array collection is a PL/SQL only data type; it cannot be used in SQL statements.

From the Oracle documentation:

Table 5-1 PL/SQL: Collection Types

Collection Type Number of Elements Index Type Dense or Sparse Uninitialized Status Where Defined Can Be ADT Attribute Data Type

Associative array (or index-by table) Unspecified String or PLS_INTEGER Either Empty In PL/SQL block or package No

VARRAY (variable-size array) Specified Integer Always dense Null In PL/SQL block or package or at schema level Only if defined at schema level

Nested table Unspecified Integer Starts dense, can become sparse Null In PL/SQL block or package or at schema level Only if defined at schema level

Collection Type	Number of Elements	Index Type	Dense or Sparse	Uninitialized Status	Where Defined	Can Be ADT Attribute Data Type
Associative array (or index-by table)	Unspecified	String or PLS_INTEGER	Either	Empty	In PL/SQL block or package	No
VARRAY (variable-size array)	Specified	Integer	Always dense	Null	In PL/SQL block or package or at schema level	Only if defined at schema level
Nested table	Unspecified	Integer	Starts dense, can become sparse	Null	In PL/SQL block or package or at schema level	Only if defined at schema level

...

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs.

...

Unlike a database table, an associative array:

Does not need disk space or network operations

Cannot be manipulated with DML statements

VARRAY and nested table collections can be used in SQL statements.