workaround for ORA-03113: end-of-file on communication channel with CAST MULTISET

Question

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?

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

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

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