ORA-03113的解决方法:使用CAST MULTISET的通信通道上的文件结束

Question

下面对TEST_FUNCTION的调用失败，并显示"ORA-03113:通信通道上的文件结束“。TEST_FUNCTION2中提供了一种解决方法。我简化了代码，因为我的实际函数要复杂得多。已在Oracle 11G上测试。有人知道第一个函数失败的原因吗？

CREATE OR REPLACE TYPE "EMPLOYEE" AS OBJECT
(
    employee_id  NUMBER(38),
    hire_date    DATE
);

CREATE OR REPLACE TYPE "EMPLOYEE_TABLE" AS TABLE OF EMPLOYEE;

CREATE OR REPLACE FUNCTION TEST_FUNCTION RETURN EMPLOYEE_TABLE IS

table1        EMPLOYEE_TABLE;
table2        EMPLOYEE_TABLE;
return_table  EMPLOYEE_TABLE;
BEGIN

SELECT CAST(MULTISET
(
    SELECT user_id, created FROM all_users
    WHERE LOWER(username) < 'm'
) AS EMPLOYEE_TABLE)
INTO table1 FROM dual;

SELECT CAST(MULTISET
(
    SELECT user_id, created FROM all_users
    WHERE LOWER(username) >= 'm'
) AS EMPLOYEE_TABLE)
INTO table2 FROM dual;

SELECT CAST(MULTISET
(    
    SELECT employee_id, hire_date
      FROM TABLE(table1)
    UNION
    SELECT employee_id, hire_date
      FROM TABLE(table2)
) AS EMPLOYEE_TABLE)
INTO return_table FROM dual;

RETURN return_table;

END TEST_FUNCTION;



CREATE OR REPLACE FUNCTION TEST_FUNCTION2 RETURN EMPLOYEE_TABLE IS

table1        EMPLOYEE_TABLE;
table2        EMPLOYEE_TABLE;
return_table  EMPLOYEE_TABLE;
BEGIN

SELECT CAST(MULTISET
(
    SELECT user_id, created FROM all_users
    WHERE LOWER(username) < 'm'
) AS EMPLOYEE_TABLE)
INTO table1 FROM dual;

SELECT CAST(MULTISET
(
    SELECT user_id, created FROM all_users
    WHERE LOWER(username) >= 'm'
) AS EMPLOYEE_TABLE)
INTO table2 FROM dual;

WITH combined AS
(
    SELECT employee_id, hire_date
      FROM TABLE(table1)
    UNION
    SELECT employee_id, hire_date
      FROM TABLE(table2)
)
SELECT CAST(MULTISET
(
    SELECT * FROM combined
) AS EMPLOYEE_TABLE)
INTO return_table FROM dual;

RETURN return_table;

END TEST_FUNCTION2;





SELECT * FROM TABLE (TEST_FUNCTION()); -- Throws exception ORA-03113.

SELECT * FROM TABLE (TEST_FUNCTION2()); -- Works

Answer 1

不确定您的特定示例，但这是Oracle版本11.2.0.1.0中的一个已知错误，它与表达式中嵌套查询的级别有关。我遇到过类似的错误，同样的查询在11.2.0.2.0 (我相信是11gR2补丁集2)中运行得很好。

https://forums.oracle.com/forums/thread.jspa?messageID=9996243