ORA-06504:PL/SQL:执行时返回结果集变量的类型

Question

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

ORA-06504：PL/SQL：返回结果集变量或查询的类型 不匹配 ORA-06512：在第 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：返回结果集变量或查询的类型 不匹配 ORA-06512：在第 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);

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

我想，您可以将它们重新组合为对象：

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类型的第二个参数而不是 ref 游标，这样调用方就不必将其批量收集到自己的本地集合中。

Answer 2

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

游标a有两列，您正在尝试将它们批量收集到单个变量中。Oracle 不会将它们包装在OBJ_TST对象中，也无法匹配它们。

为什么要使用游标：

CREATE OR REPLACE PROCEDURE TST_OBJ  (
input_date IN  DATE,
out_objs   OUT OBJ_TBL
)
AS   
BEGIN
SELECT OBJ_TST( col_id, col_date)
BULK COLLECT INTO out_objs
FROM   MY_TBL
WHERE  create_date BETWEEN input_date AND input_date + 1;
END TST_OBJ;
/

然后你可以做：

DECLARE
var1   OBJ_TBL; 
BEGIN
TST_OBJ (
input_date => DATE '2017-08-21',
out_objs   => var1
);
For rec in 1..var1.count LOOP     
DBMS_OUTPUT.put_line (var1(rec).col_id  ||' '|| var1(rec).Col_DATE);
END LOOP;
END;
/