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
