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

Solution

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

Comments

[Justin Cave]

Is there a reason that you're using the CAST( MULTISET syntax and selecting from dual rather than doing the (seemingly more straightforward) BULK COLLECT operation? And then doing a MULTISET UNION in PL/SQL rather than passing the collections back to the SQL engine just to get the data back into PL/SQL?

[DazzaL]

+1 for contacting support if your workaround isn't suitable. your bug seems to match a registered bug "Bug 14173524 : QUERY WITH UNION INSIDE THE CAST(MULTISET()) CRASHE WITH ORA-07445[QCSTDA()]" / "Bug 8560880 : PL/SQL CODE BLOCK CAUSING ORA-07445 QCSFSQACN"