workaround für ORA-03113: end-of-file on communication channel mit CAST MULTISET

Den Aufruf TEST_FUNCTION unter dem Fehler "ORA-03113: end-of-file on communication channel". Ein workaround vorgestellt, in TEST_FUNCTION2. Ich lief dann den code als meine eigentliche Funktion ist wesentlich komplexer. Getestet auf Oracle 11G. Jemand eine Idee, warum die erste Funktion fehlschlägt?

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

InformationsquelleAutor Jefferstone | 2012-12-10