oracle ORA-03113 的解决方法：使用 CAST MULTISET 的通信通道上的文件结尾
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13810802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
workaround for ORA-03113: end-of-file on communication channel with CAST MULTISET
提问by Jefferstone
The call to TEST_FUNCTION below fails with "ORA-03113: end-of-file on communication channel". A workaround is presented in TEST_FUNCTION2. I boiled down the code as my actual function is far more complex. Tested on Oracle 11G. Anyone have any idea why the first function fails?
下面对 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
回答by user2011104
Not sure about your particular example but this is a known bug in Oracle version 11.2.0.1.0 It has something to do with the level of nested queries in an expression. I have faced similar error and the same query runs fine in 11.2.0.2.0 (11gR2 patchset 2 I believe).
不确定您的特定示例，但这是 Oracle 版本 11.2.0.1.0 中的一个已知错误它与表达式中嵌套查询的级别有关。我遇到了类似的错误，并且相同的查询在 11.2.0.2.0（我相信是 11gR2 补丁集 2）中运行良好。
https://forums.oracle.com/forums/thread.jspa?messageID=9996243
https://forums.oracle.com/forums/thread.jspa?messageID=9996243
