Oracle Associative Array TYPE is not able to use in USING statement (If TYPE is declared within Package)

Question

Hi All,

If 'Associative Array variable' is declared globally, able to use that in OPEN CURSOR USING statement.

If 'Associative Array variable' is declared within package, while use in OPEN CURSOR USING statement, getting compilation error.

More details provided below

I am storing some values in one Associative Array variable. Later iterating those values by taking them into Cursor like below,

   strQuery := 'select DISTINCT column_value from table(CAST(:v_Assoc_Collection AS AssocArray_Date_t))';
   OPEN RefCur_Item FOR strQuery USING v_Assoc_Collection;

Here i used the Associative array variable 'v_Assoc_Collection' which is a type of 'AssocArray_Date_t'.

If i declared that type 'AssocArray_Date_t', outside the package [ CREATE OR REPLACE TYPE AssocArray_Date_t IS TABLE OF DATE; ], then it is working properly.

But, If i declare the type within the procedure, am getting compilation error.

Error: PLS-00457: expressions have to be of SQL types. In : "OPEN RefCur_Item FOR strQuery USING v_Assoc_Collection;"

Error: PL/SQL: Statement ignored. In : "OPEN RefCur_Item FOR strQuery USING v_Assoc_Collection;"

For reference, code is provided below,

-- Package Declaration CREATE OR REPLACE PACKAGE AssocTypePackage AS

Type CursorType IS REF CURSOR;
PROCEDURE AssocTypeProcedure(name IN VARCHAR2); END;

-- Package Body CREATE OR REPLACE PACKAGE BODY AssocTypePackage AS

PROCEDURE AssocTypeProcedure(name IN VARCHAR2) IS
strQuery VARCHAR2(4000); v_Assoc_Collection AssocArray_Date_t := AssocArray_Date_t(); BEGIN

   FOR i IN 1..5
   LOOP      
           v_Assoc_Collection.EXTEND;
           v_Assoc_Collection(v_Assoc_Collection.COUNT)

:= <>;
END LOOP;

   strQuery := 'select DISTINCT column_value from

table(CAST(:v_Assoc_Collection AS AssocArray_Date_t))'; OPEN RefCur_Item FOR strQuery USING v_Assoc_Collection; LOOP -- ---- -- some processing -- ---- END LOOP;

END AssocTypeProcedure;

END AssocTypePackage;

Your replies will help a lot.
Thanks in advance.

Answer 1

You should be aware that in Oracle, the SQL engine and the PL/SQL engine are two seperate things, though they can call each other. To use arrays in SQL statements, they have to be visible to the SQL engine, i.e. they have to be declared as SQL types using the CREATE TYPE statement. Types created within a package are simply invisible to the SQL engine.