Home » SQL & PL/SQL » SQL & PL/SQL » ora-06504 PL/SQL: Return types of Result Set variables or query do not match
ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276599] Thu, 25 October 2007 10:33 Go to next message
v4goel
Messages: 4
Registered: October 2007
Junior Member

Hi,

I am trying to call a procedure B from another procedure A.
Procedure B returns a ref cursor.
Now I need to fetch this cursor and further join with other table
in procedure A. and finally open the resulting cursor.
My code has bit complex coding so I first tried to do this on simple table / queries but the error is same.

Exception is coming when I do FETCH and BULK COLLECT.
given below is the simple one code.


CREATE OR REPLACE TYPE MYTESTOBJ AS OBJECT
(
CAT_ID NUMBER(10),
CAT_NAME VARCHAR2(50)
);


CREATE OR REPLACE TYPE MYTESTTABLE AS TABLE OF MYTESTOBJECT;


CREATE OR REPLACE PACKAGE PCK_TEST AS
TYPE PCK_CURSOR IS REF CURSOR;
PROCEDURE PROC_A
(
TESTID NUMBER,
OUTCUR_A OUT PCK_CURSOR
);

PROCEDURE PROC_B
(
TESTID2 NUMBER,
OUTCUR_B OUT PCK_CURSOR
);
END PCK_TEST;


CREATE OR REPLACE PACKAGE BODY PCK_TEST AS
PROCEDURE PROC_A
(
TESTID NUMBER,
OUTCUR_A OUT PCK_CURSOR
);
IS
CATDATA MYTESTTABLE;
MYCATCUR PCK_CURSOR;

BEGIN
PROC_B(TESTID, MYCATCUR);
FETCH MYCATCUR BULK COLLECT INTO CATDATA;
CLOSE MYCATCUR;
OPEN CATCUR FOR SELECT * FROM TABLE(CAST(CATDATA AS MYTESTTABLE)) TAB1,
CATCOUNT TAB2 WHERE TAB1.CAT_ID=TAB2.CAT_ID;
END PROC_A;

PROCEDURE PROC_B
(
TESTID2 NUMBER,
OUTCUR_B OUT PCK_CURSOR
);
IS
BEGIN
OPEN OUTCUR_B FOR
SELECT CAT_ID, CAT_NAME FROM GLOBAL_CAT;
END PROC_B;

END PCK_TEST;


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

Where I am doing wrong? please help
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276606 is a reply to message #276599] Thu, 25 October 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276680 is a reply to message #276599] Thu, 25 October 2007 23:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The documentation states it in FETCH Statement description in PL/SQL User's Guide and Reference:
Quote:

Restrictions on BULK COLLECT
The following restrictions apply to the BULK COLLECT clause:
****
* When an implicit datatype conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK COLLECT INTO clause.
Do not use week cursor PCK_CURSOR, create new cursor type that RETURN mytesttable and use it instead.
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276746 is a reply to message #276680] Fri, 26 October 2007 04:15 Go to previous messageGo to next message
v4goel
Messages: 4
Registered: October 2007
Junior Member
Hi flyboy,

but when I give
TYPE PCK_CURSOR IS REF CURSOR RETURN MYTESTTABLE%ROWTYPE

I get compilation error
PLS-00310: with %ROWTYPE attribute, 'MYTESTTABLE' must name a table, cursor or cursor-variable


Also I would like to ask this. Is it what I am trying to do is correct. I mean is returning a REF cursor and then using it in another procedure SQL join.
I have earlier done similar thing but then the cursor was explicit cursor defined in the procedure itself, and it worked.

Thanks for your help
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276777 is a reply to message #276599] Fri, 26 October 2007 05:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

PLS-00310: with %ROWTYPE attribute, 'MYTESTTABLE' must name a table, cursor or cursor-variable
%ROWTYPE provides a record type that represents a row in a given database table.
MYTESTTABLE is already the TYPE, so it makes no sense to issue %ROWTYPE on MYTESTTABLE.
Quote:

I have earlier done similar thing but then the cursor was explicit cursor defined in the procedure itself, and it worked.
I do not know, what you did, so it is hard to tell, why it worked.

You know, that fetching all those rows requires extra time and space?
Why not simply SELECT directly from the GLOBAL_CAT table?
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276812 is a reply to message #276777] Fri, 26 October 2007 06:28 Go to previous messageGo to next message
v4goel
Messages: 4
Registered: October 2007
Junior Member
Hi,

I tell you the actual problem.
I have a 2 packages say A and B.
package A does lots of things and finally opens a cursor for a dynamic SQL. so package A is sort a global subroutine for getting the records for me.
Now I am making another procedure B. and I need to call procedure A inside the procedure B, because I need those records it returns.
Now the procedure B is supposed to output two cursors.

1. first one having all the records returned from procedure A
2. second one need to join the cursor returned from A with some other table and open the resulting cursor.

I can not change the procedure B. so I was trying similar thing with sample code first. But I am not able to correctly typecast the cursor and collection.

The sample tables and object structures are already given in the post.
Can you suggest me something here please.

[Updated on: Fri, 26 October 2007 06:31]

Report message to a moderator

Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276850 is a reply to message #276599] Fri, 26 October 2007 08:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

MYTESTTABLE is already the TYPE, so it makes no sense to issue %ROWTYPE on MYTESTTABLE.
Maybe I should be more clear in my hints.
Why not simply omit the %ROWTYPE and issue
TYPE PCK_MYTESTTABLE_CURSOR IS REF CURSOR RETURN MYTESTTABLE
Note I changed the type name as you use PCK_CURSOR for OUTCUR_A in PROC_A, and it has different columns than OUTCUR_B.

I would use PIPELINED function returning values from PROC_B cursor; then CAST its result in SELECT.
But, if the PROC_B does not return many rows and you are satisfied with performance, even this solution is possible.
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276931 is a reply to message #276850] Sat, 27 October 2007 01:41 Go to previous messageGo to next message
v4goel
Messages: 4
Registered: October 2007
Junior Member
TYPE PCK_MYTESTTABLE_CURSOR IS REF CURSOR RETURN MYTESTTABLE

gives
Error(3,20): PLS-00362: invalid cursor return type; 'MYTESTTABLE' must be a record type


I am really confused here. Anyway I am going to try "PIPELINED function" as you suggested.
Thanks for your time.
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276932 is a reply to message #276599] Sat, 27 October 2007 02:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

Error(3,20): PLS-00362: invalid cursor return type; 'MYTESTTABLE' must be a record type
What is not clear with this error message?
And after consulting Using Cursor Variables (REF CURSORs) in the documentation you shall realize, that CURSOR shall return RECORD type.
In your example, MYTESTTABLE is a collection of MYTESTOBJECT records.
Which of these two will you pick up? I picked the wrong one (just re-typed, but no excuse for not thinking about its meaning).

Also read about OBJECT types in PL/SQL User's Guide and Reference, I am afraid, you will need more changes in your code than this.
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #658057 is a reply to message #276931] Thu, 01 December 2016 12:32 Go to previous messageGo to next message
renebpr
Messages: 1
Registered: December 2016
Junior Member
Hi v4goel...

Tell me...

How did you solve it without changing the second parameter of PROC_B and holding FETCH MYCATCUR BULK COLLECT INTO CATDATA (obviously, without changing the body funtion)?

I did the following in your function PROC_B....

before...
PROCEDURE PROC_B
(
TESTID2 NUMBER,
OUTCUR_B OUT PCK_CURSOR
);
IS
BEGIN
OPEN OUTCUR_B FOR
SELECT CAT_ID, CAT_NAME FROM GLOBAL_CAT;
END PROC_B;

after...
PROCEDURE PROC_B
(
TESTID2 NUMBER,
OUTCUR_B OUT PCK_CURSOR
);
IS
BEGIN
OPEN OUTCUR_B FOR
SELECT MYTESTOBJ(CAT_ID, CAT_NAME) FROM GLOBAL_CAT;
END PROC_B;


that´s all.. but.... repeat.. without change the funtion... in this case... without change PROC_B...

how to do the same that i put in the function PROC_B but out of PROC_B... that's to say .. for example in the line: FETCH MYCATCUR BULK COLLECT INTO CATDATA

how.....

tks....
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #658058 is a reply to message #658057] Thu, 01 December 2016 12:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
This thread is ONLY nine YEARS old

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #658059 is a reply to message #658057] Thu, 01 December 2016 12:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As BlackSwan suggested better create your own topic, so repost clearly explaining your issue with a test case (read this link) and formatting the code (read the links BlackSwan gave you) without forgetting to post your Oracle version (with 4 decimals like 11.2.0.4).

Previous Topic: Function MAX
Next Topic: dynamic sql to grant privilege
Goto Forum:
  


Current Time: Sat Dec 03 13:41:06 CST 2016

Total time taken to generate the page: 0.08007 seconds