oracle - ORA-06504 : PL/SQL: Return types of Result Set variables while execution

Question

我创建了一个对象和过程，如下所示，在执行时出现以下错误。

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 8

CREATE OR REPLACE TYPE OBJ_TST AS OBJECT
(
   COl_ID NUMBER (30, 0),
   Col_DATE TIMESTAMP (6)
);
/

create or replace TYPE OBJ_TBL AS TABLE OF OBJ_TST;

/

CREATE OR REPLACE PROCEDURE TST_OBJ  (input_date IN     DATE,
                                      out_cur     OUT SYS_REFCURSOR )                                    
AS   
   l_tab    OBJ_TBL  := OBJ_TBL ();
BEGIN

   SELECT OBJ_TST (ti.col_id, ti.col_date)
     BULK COLLECT INTO l_tab
     FROM MY_TBL ti
    WHERE ti.create_date BETWEEN input_date AND input_date + 1;

   Open o_cur for select col_id,col_date from table(l_tab);

END TST_OBJ;
/

执行给我带来了上述错误。 MY_TBL 的列数据类型(col_id 和 col_date)与我的对象相同。

DECLARE
   a      SYS_REFCURSOR;
   var1   OBJ_TBL; 

BEGIN
   TST_OBJ (input_date => '21-Aug-2017', out_cur => a);

  FETCH a bulk collect INTO var1;

  For rec in 1..var1.count
   LOOP     
      DBMS_OUTPUT.put_line (var1(rec).col_id  ||' '|| var1(rec).Col_DATE);
   END LOOP;
END;

/

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 8

但是，当我像这样执行时，它工作正常:

DECLARE
   a      SYS_REFCURSOR;
   var1   NUMBER;
   var2   TIMESTAMP (6);
BEGIN
   TST_OBJ (i_date => '21-Aug-2017', out_cur => a);

   LOOP
      FETCH a INTO var1, var2;

      EXIT WHEN a%NOTFOUND;

      DBMS_OUTPUT.put_line (var1 ||' '|| var2);
   END LOOP;
END;

任何人都可以建议这里出了什么问题吗？

Answer 1

您正在使用表集合表达式来取消嵌套表集合:

Open out_cur for select col_id,col_date from table(l_tab);

查询返回两个关系列，而不是单个对象，因此您的光标也有两列。尝试将两个关系列批量收集到匿名 block 中的单个对象中会引发异常。

我想，你可以将它们重新组合为对象:

Open out_cur for select OBJ_TST(col_id,col_date) from table(l_tab);

或者如果您不想显式列出列/字段名称:

Open out_cur for select cast(multiset(select * from table(l_tab)) as obj_tbl) from dual;

但是在您的示例中，拥有表类型有点毫无意义，您可以这样做:

CREATE OR REPLACE PROCEDURE TST_OBJ  (input_date IN     DATE,
                                      out_cur     OUT SYS_REFCURSOR )
AS   
BEGIN

   Open out_cur for
   SELECT OBJ_TST (ti.col_id, ti.col_date)
     FROM MY_TBL ti
    WHERE ti.create_date BETWEEN input_date AND input_date + 1;

END TST_OBJ;
/

但我想象您对函数内的集合还有一些其他用途 - 在查询和返回它之前修改它。或者你可以设置 OBJ_TBL 的第二个参数类型而不是引用游标，因此调用者不必将其批量收集到自己的本地集合中。