ora-06504 PL/SQL: Return types of Result Set variables or query do not match [message #276599] |
Thu, 25 October 2007 10:33 |
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 #276680 is a reply to message #276599] |
Thu, 25 October 2007 23:31 |
flyboy
Messages: 1903 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 |
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 |
flyboy
Messages: 1903 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 |
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 |
flyboy
Messages: 1903 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 #276932 is a reply to message #276599] |
Sat, 27 October 2007 02:13 |
flyboy
Messages: 1903 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.
|
|
|
|
|
|