Oracle关联数组TYPE无法在USING语句中使用（如果在Package中声明TYPE）

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

如果全局声明'Associative Array变量',则能够在OPEN CURSOR USING语句中使用它。

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

如果在包内声明'关联数组变量',则在OPEN CURSOR USING语句中使用时,会出现编译错误。

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,

我将一些值存储在一个Associative Array变量中。稍后通过将它们带入Cursor来迭代这些值,如下所示,

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'.

这里我使用了关联数组变量'v_Assoc_Collection',这是一种'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.

如果我声明类型'AssocArray_Date_t',在包外[创建或替换类型AssocArray_Date_t是表日期; ],然后它正常工作。

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;"

错误:PLS-00457:表达式必须是SQL类型。在:“OPEN RefCur_Item FOR strQuery USING v_Assoc_Collection;”

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

错误:PL / SQL:语句被忽略。在:“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) := <<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;

1 个解决方案

#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.

您应该知道在Oracle中,SQL引擎和PL / SQL引擎是两个独立的东西,尽管它们可以相互调用。要在SQL语句中使用数组,它们必须对SQL引擎可见,即必须使用CREATE TYPE语句将它们声明为SQL类型。在包中创建的类型对于SQL引擎是不可见的。