Error (ORA-21700) with Table Operator after updating to Oracle 12.2 from 12.1 [duplicate]

All my research shows that what we are doing was introduced in 12.1 and should still work in 12.2.

Yes this is true. Prior to Oracle 12c, you cannot use associate arrays in the scope of SQL statements within a PLSQL block. However, Oracle make sure that when it introduces new version, old one doesnot get affected. I tried to test your code and its working fine at my end. Looks issue is somewhere else, might be some issue while using C#. See below demo:

My Oracle Version:

SQL> select * from v$version;BANNER------     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Table Data:

SQL>SELECT * from TEST;  col  ---   1   2   3

Package:

--Package SpecificationCREATE OR REPLACE PACKAGE TESTTTASTYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;Procedure GetReadingStatus (                          STATUSID_ARR IN NUMBER_ARRAY,                          P_RETURNS OUT SYS_REFCURSOR                            );END;/--Package BodyCREATE OR REPLACE PACKAGE BODY TESTTTASPROCEDURE GetReadingStatus(                          STATUSID_ARR IN NUMBER_ARRAY,                          P_RETURNS OUT SYS_REFCURSOR                            )Is                        BEGIN  OPEN P_RETURNS FOR    SELECT *     FROM TEST     where col IN (SELECT * FROM TABLE(STATUSID_ARR));END;END TESTTT;

Calling:

DECLAREvar  TESTTT.NUMBER_ARRAY;v_out sys_refcursor;num  NUMBER;BEGINvar(1):= '1';var(2):= '2'; TESTTT.GetReadingStatus(STATUSID_ARR=>var,                         P_RETURNS =>v_out); Loop fetch v_out INTO num; exit WHEN v_out%notfound; dbms_output.put_line('Return From Procdure--'||num); end loop;end;

Output:

Return From Procdure--1Return From Procdure--2

I encountered the same or a similar issue, after upgrading from Oracle 12c to 19c. I'm not sure why the Oracle upgrade caused a problem, and I also don't really understand why my fix works!

In my stored procedures, where Oracle's TABLE function is applied to some stored procedure input, I get the error: "ORA-21700: object does not exist or is marked for delete".

However, where Oracle's TABLE function was applied to a local variable within the stored procedure, there was no error. So my workaround was simply to assign stored procedure inputs to local variables, before using the TABLE function, and somehow this resolved the issue!

CREATE OR REPLACE PACKAGE my_types IS  TYPE integers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  TYPE reals    IS TABLE OF FLOAT INDEX BY BINARY_INTEGER;END my_types;/CREATE OR REPLACE PROCEDURE order_list(  i_order_numbers  IN  my_types.integers,  o_order_numbers  OUT my_types.integers,  o_order_values   OUT my_types.reals)IS  r_order_numbers  my_types.integers;  CURSOR order_list_cur (p_order_numbers my_types.integers)      IS  SELECT order_number, order_value    FROM orders   WHERE order_number IN (SELECT * FROM TABLE(p_order_numbers))  ;  order_list_rec  order_list_cur%ROWTYPE;  rec_no BINARY_INTEGER;BEGIN  r_order_numbers := i_order_numbers;  rec_no := 0;  OPEN order_list_cur(r_order_numbers);  LOOP    FETCH order_list_cur INTO order_list_rec;    EXIT WHEN order_list_cur%NOTFOUND;      rec_no := rec_no + 1;       o_order_numbers(rec_no) := order_list_rec.order_number;       o_order_values(rec_no) := order_list_rec.order_value;  END LOOP;  CLOSE order_list_cur;END order_list;

this question is quite like my situation when I got the same error with 12.2 but not 12.1. I have posted my answer here since that one is using package instead of schema defined type. Maybe this issue can be solve the same way. Just try to add a temp variable of the same type and assign the parameter to it.