title: workaround for ORA-03113: end-of-file on communication channel with CAST MULTISET
body: <p>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?</p>
  
  <pre><code>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) &lt; 'm'
  ) AS EMPLOYEE_TABLE)
  INTO table1 FROM dual;
  
  SELECT CAST(MULTISET
  (
      SELECT user_id, created FROM all_users
      WHERE LOWER(username) &gt;= '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) &lt; 'm'
  ) AS EMPLOYEE_TABLE)
  INTO table1 FROM dual;
  
  SELECT CAST(MULTISET
  (
      SELECT user_id, created FROM all_users
      WHERE LOWER(username) &gt;= '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
  </code></pre>