从12.1更新到Oracle 12.2后，表运算符出现错误(ORA-21700) [英] Error (ORA-21700) with Table Operator after updating to Oracle 12.2 from 12.1

问题描述

我们的Oracle数据库最近从12.1.0.2升级到12.2.0.1 +补丁集更新20180417.

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

自更新以来，我们在调用plsql过程时遇到以下错误: ORA-21700:对象不存在或被标记为删除

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

我们已经缩小了问题的范围，这似乎是由于在程序包中定义的关联数组上使用 table运算符引起的.我所有的研究都表明，我们所做的工作是在12.1中引入的，并且仍然应该在12.2中起作用.

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.

以下是失败的过程的简化版本以及相关的类型定义.使用托管数据访问从c#代码中调用它.

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;

这是一个失败的过程:

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;

如果删除select * from table(STATUSID_ARR)部分，它将运行.

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

在12.2中的关联数组上使用表运算符是否存在问题?可能是其他原因引起的吗?

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

推荐答案

我的所有研究表明，我们所做的工作是在12.1中引入的 并仍可在12.2中使用.

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

是是真的.在Oracle 12c之前，不能在PLSQL块内的SQL语句范围内使用关联数组.但是，Oracle确保在引入新版本时不会影响旧版本.我尝试测试您的代码，并在最后完成了工作.看起来问题在其他地方，使用C#可能是问题.参见下面的演示:

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:

我的Oracle版本:

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

这篇关于从12.1更新到Oracle 12.2后，表运算符出现错误(ORA-21700)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！