Oracle Associative Array cannot use TYPE in USING statement (if TYPE is declared in package)

Question

If the variable "Associative variable" declared declared globally , it can use it in the expression OPEN CURSOR USING.

If the variable "associative variable" is declared in the package , and used in the expression OPEN CURSOR USING, receiving a compilation error.

See below for more details.

I store some values ​​in one variable of an associative array. Later, iterating over these values, taking them in Cursor, as shown 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 of type 'AssocArray_Date_t'.

If I declared the type 'AssocArray_Date_t' outside the package [ CREATE OR REPLACE TYPE AssocArray_Date_t IS TABLE OF DATE;], it works correctly.

But if I declare the type inside the procedure , I get a compilation error.

Error: PLS-00457: expressions must be of type SQL. In: "OPEN RefCur_Item FOR strQuery USING v_Assoc_Collection;"

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

For reference, below is the code,

    -- 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) := <<someDate>>;               
  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;

Answer 1

, Oracle SQL PL/SQL - , . SQL, SQL, .. SQL CREATE TYPE. , , SQL.