I'm trying to fetch data from a sys_refcursor from two other procs that are executed in the below proc into a pl/sql table. I've defined the pl/sql table as below. But, I'm getting the below error. I've made the pl/sql table that i created of the same type. The query that produces the output for "out_cur_1" and "out_cur_2" are union of multiple queries. out_cur_1 cursor would have the result set with a code and description and out_cur_2 would have the result set with a code, description and a type. Can somebody help if I'm missing something here.....
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE test1_rec AS OBJECT
(
code CHAR(10),
Description CHAR(100)
)
;
CREATE OR REPLACE TYPE tab1 AS TABLE OF test1_rec;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE test2_rec AS OBJECT
(
Code CHAR(10),
Description CHAR(100),
Type CHAR(2)
)
;
CREATE OR REPLACE TYPE tab2 AS TABLE OF test2_rec;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE pcg_test
(
out_msg OUT CHAR,
out_cur_test OUT SYS_REFCURSOR
)
IS
out_cur_1 SYS_REFCURSOR;
out_cur_2 SYS_REFCURSOR;
v_1_list tab1 := tab1();
v_2_list tab2 := tab2();
BEGIN
vrs.pack_test.pcg_sample1
(
o_msg => out_msg
,o_cur => out_cur_1
);
FETCH out_cur_1 BULK COLLECT INTO v_1_list;
vrs.pack_test.pcg_sample2
(
out_msg => out_msg
,out_cur => out_cur_2
);
FETCH out_cur_2 BULK COLLECT INTO v_2_list;
OPEN out_cur_test FOR
SELECT a.code AS code,
a. AS name
FROM TABLE(CAST(v_1_list AS tab1)) a
UNION
SELECT a.Code AS code,
a.Description AS name
FROM TABLE(CAST(v_2_list AS tab2)) a;
EXCEPTION
WHEN OTHERS THEN
out_msg := SUBSTR(SQLERRM, 1, 9); /* Return error */
END pcg_test;
------------------------------------------------------------------------------------------------------------