CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
XING
XING

Reputation: 9886

ORA-06504: PL/SQL: Return types of Result Set variables while execution

I created an Object and procedure as below and while execution i get the below error.

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

CREATE OR REPLACE TYPE OBJ_TST AS OBJECT
(
   COl_ID NUMBER (30, 0),
   Col_DATE TIMESTAMP (6)
);
/

create or replace TYPE OBJ_TBL AS TABLE OF OBJ_TST;

/

CREATE OR REPLACE PROCEDURE TST_OBJ  (input_date IN     DATE,
                                      out_cur     OUT SYS_REFCURSOR )                                    
AS   
   l_tab    OBJ_TBL  := OBJ_TBL ();
BEGIN

   SELECT OBJ_TST (ti.col_id, ti.col_date)
     BULK COLLECT INTO l_tab
     FROM MY_TBL ti
    WHERE ti.create_date BETWEEN input_date AND input_date + 1;

   Open o_cur for select col_id,col_date from table(l_tab);

END TST_OBJ;
/

Execution brings me the above mentioned error. MY_TBL has column data type of (col_id and col_date) same as of my object.

DECLARE
   a      SYS_REFCURSOR;
   var1   OBJ_TBL; 

BEGIN
   TST_OBJ (input_date => '21-Aug-2017', out_cur => a);

  FETCH a bulk collect INTO var1;

  For rec in 1..var1.count
   LOOP     
      DBMS_OUTPUT.put_line (var1(rec).col_id  ||' '|| var1(rec).Col_DATE);
   END LOOP;
END;

/

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

However when i excute like this it works fine:

DECLARE
   a      SYS_REFCURSOR;
   var1   NUMBER;
   var2   TIMESTAMP (6);
BEGIN
   TST_OBJ (i_date => '21-Aug-2017', out_cur => a);

   LOOP
      FETCH a INTO var1, var2;

      EXIT WHEN a%NOTFOUND;

      DBMS_OUTPUT.put_line (var1 ||' '|| var2);
   END LOOP;
END;

Can anyone please suggest whats wrong here ?

Upvotes: 2

Views: 13569

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You're using a table collection expression to unnest your table collection:

Open out_cur for select col_id,col_date from table(l_tab);

The query is returning two relational columns, not a single object, so your cursor has two columns too. Trying to bulk collect two relational columns into a single object in your anonymous block is throwing the exception.

You could, I suppose, recombine them as objects:

Open out_cur for select OBJ_TST(col_id,col_date) from table(l_tab);

or if you don't want to explicitly list the column/field names:

Open out_cur for select cast(multiset(select * from table(l_tab)) as obj_tbl) from dual;

But then in your example having the table type is a bit pointless, and you can just do:

CREATE OR REPLACE PROCEDURE TST_OBJ  (input_date IN     DATE,
                                      out_cur     OUT SYS_REFCURSOR )
AS   
BEGIN

   Open out_cur for
   SELECT OBJ_TST (ti.col_id, ti.col_date)
     FROM MY_TBL ti
    WHERE ti.create_date BETWEEN input_date AND input_date + 1;

END TST_OBJ;
/

But I image you have some other use for the collection inside the function - modifying it before querying and returning it. Or you could make the second argument of OBJ_TBL type instead of a ref cursor, so the caller doesn't have to bulk collect that into its own local collection itself.

Upvotes: 4

MT0
MT0

Reputation: 167981

DECLARE
   a      SYS_REFCURSOR;
   var1   OBJ_TBL; 

BEGIN
   TST_OBJ (input_date => '21-Aug-2017', out_cur => a);

  FETCH a bulk collect INTO var1;

  For rec in 1..var1.count
   LOOP     
      DBMS_OUTPUT.put_line (var1(rec).col_id  ||' '|| var1(rec).Col_DATE);
   END LOOP;
END;
/

The cursor a has two columns and you are trying to bulk collect them into a single variable. Oracle will not wrap them in a OBJ_TST object and it can't match them.

Why use cursors at all:

CREATE OR REPLACE PROCEDURE TST_OBJ  (
  input_date IN  DATE,
  out_objs   OUT OBJ_TBL
)
AS   
BEGIN
   SELECT OBJ_TST( col_id, col_date)
   BULK COLLECT INTO out_objs
   FROM   MY_TBL
   WHERE  create_date BETWEEN input_date AND input_date + 1;
END TST_OBJ;
/

Then you can just do:

DECLARE
  var1   OBJ_TBL; 
BEGIN
  TST_OBJ (
    input_date => DATE '2017-08-21',
    out_objs   => var1
  );

  For rec in 1..var1.count LOOP     
    DBMS_OUTPUT.put_line (var1(rec).col_id  ||' '|| var1(rec).Col_DATE);
  END LOOP;
END;
/

Upvotes: 1

Related Questions