solution de contournement pour les ORA-03113: fin de fichier sur le canal de communication avec des acteurs MULTISET

L'appel à TEST_FUNCTION ci-dessous échoue avec "ORA-03113: fin de fichier sur le canal de communication". Une solution de contournement est présenté dans TEST_FUNCTION2. J'ai cuit le code de ma fonction réelle est beaucoup plus complexe. Testé sur Oracle 11G. Quelqu'un a une idée pourquoi la première fonction échoue?

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

OriginalL'auteur Jefferstone | 2012-12-10