Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetch data from a sys_refcursor to a pl/sql table

2792904Nov 11 2014 — edited Nov 12 2014

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.....

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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;

------------------------------------------------------------------------------------------------------------    

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 10 2014
Added on Nov 11 2014
5 comments
2,515 views