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

Question

Our Oracle database was recently updated from 12.1.0.2 to 12.2.0.1 + patch set update 20180417.

Ever since the update we are getting the following error when calling a plsql procedure: ORA-21700: object does not exist or is marked for delete

We have narrowed down the issue and it seems to be caused by using the table operator on an associative array defined within the package. All my research shows that what we are doing was introduced in 12.1 and should still work in 12.2.

Below is a simplified version of a procedure that is failing along with the related type definition. It is being called from c# code using managed data access.

Here is the associative array type definition in the package:

TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Here is a procedure that fails:

PROCEDURE GetReadingStatus(
  STATUSID_ARR IN NUMBER_ARRAY,
  P_RETURNS OUT SYS_REFCURSOR    
)
BEGIN
  OPEN P_RETURNS FOR
    SELECT * FROM READINGSTATUS rs
    WHERE rs.statusID IN (select * from table(STATUSID_ARR));
END;

It runs if the select * from table(STATUSID_ARR) portion is removed.

Is there an issue with using the table operator on associative arrays in 12.2? Could the issue be stemming from something else?

Answer 1

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;

Answer 2

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 Specification
CREATE OR REPLACE PACKAGE TESTTT
AS
TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Procedure GetReadingStatus (
                          STATUSID_ARR IN NUMBER_ARRAY,
                          P_RETURNS OUT SYS_REFCURSOR    
                        );
END;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY TESTTT
AS
PROCEDURE 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:

DECLARE
var  TESTTT.NUMBER_ARRAY;
v_out sys_refcursor;
num  NUMBER;

BEGIN

var(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--1
Return From Procdure--2