Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00382: expression is of wrong type (Oracle 10 on Windows)
PLS-00382: expression is of wrong type [message #399938] Fri, 24 April 2009 10:52 Go to next message
trolands
Messages: 4
Registered: April 2009
Location: Wisconsin, USA
Junior Member
I am new to Oracle and running into a problem I just don't understand. It started with trying to pull data from my VB.NET code into an OracleDataReader. The select statement would just spin and never return data when I specified any column names. If I tried select *, data is returned.

So I try it inside of a package (based on what I could quickly figure out from different examples)....

CREATE OR REPLACE
PACKAGE BODY AVA_KONOPWEB AS

PROCEDURE SP_GETPRODUCTSBYCATEGORY ( p_categoryId IN NUMBER DEFAULT 1, cv_products OUT ProductsCurTyp ) IS
BEGIN
OPEN cv_products FOR
SELECT recipekeyid, recipehierarchyid, recipename, recipeshortdescription, isinactive
FROM hs_vrecipelist
WHERE isinactive = 0 AND recipehierarchyid = p_categoryId
ORDER BY recipeshortdescription;

END SP_GETPRODUCTSBYCATEGORY;

END AVA_KONOPWEB;


When I compile this, I get the error, PLS-00382: expression is of wrong type, on the line where the select statement begins. If I change that select statement to select *, it compiles just fine.

What's going on? What am I missing? Any help on this greatly appreciated!

Tim
Re: PLS-00382: expression is of wrong type [message #399941 is a reply to message #399938] Fri, 24 April 2009 10:56 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
Tim,

What is ProductsCurTyp defined as?


Thanks,
Rajesh.
Re: PLS-00382: expression is of wrong type [message #399942 is a reply to message #399941] Fri, 24 April 2009 10:58 Go to previous messageGo to next message
trolands
Messages: 4
Registered: April 2009
Location: Wisconsin, USA
Junior Member
Here is the actual package creation....

create or replace PACKAGE AVA_KONOPWEB AS

/* TODO enter package declarations (types, exceptions, methods etc) here */
TYPE ProductsCurTyp IS REF CURSOR RETURN hs_vrecipelist%ROWTYPE;

PROCEDURE SP_GETPRODUCTSBYCATEGORY ( p_categoryId IN NUMBER DEFAULT 1, cv_products OUT ProductsCurTyp );

END AVA_KONOPWEB;
Re: PLS-00382: expression is of wrong type [message #399943 is a reply to message #399938] Fri, 24 April 2009 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is clear that ProductsCurTyp is a ref cursor for all the columns and not some of them.

Regards
Michel
Re: PLS-00382: expression is of wrong type [message #399945 is a reply to message #399938] Fri, 24 April 2009 11:02 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
Can you try declaring "cv_products OUT" as ref cursor instead ?
Eg.


  1  create or replace procedure test_proc(i_dept_id in number,
  2  cur_dname out sys_refcursor) is
  3  begin
  4  open cur_dname for
  5  Select * from dept where dept_id = i_dept_id;
  6* end;
ops$rchamarthi> /
 
Procedure created.



Re: PLS-00382: expression is of wrong type [message #399947 is a reply to message #399945] Fri, 24 April 2009 11:09 Go to previous messageGo to next message
trolands
Messages: 4
Registered: April 2009
Location: Wisconsin, USA
Junior Member
Rajesh,

That worked! Thank you so much!

Tim
Re: PLS-00382: expression is of wrong type [message #399948 is a reply to message #399947] Fri, 24 April 2009 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trolands wrote on Fri, 24 April 2009 18:09
Rajesh,

That worked! Thank you so much!

Tim

Except that you change a strongly typed cursor to a weakly typed one and this is surely what want your client if he defined a cursor type.
Something that "works" may not be something to do.

Regards
Michel

Re: PLS-00382: expression is of wrong type [message #399973 is a reply to message #399948] Fri, 24 April 2009 13:34 Go to previous messageGo to next message
trolands
Messages: 4
Registered: April 2009
Location: Wisconsin, USA
Junior Member
Michel,

You are correct, of course. This did allow me to test returning data to my application via a stored procedure, however. Turns out it also does not work. The data is never returned, and the web application eventually times out. I can run the same query through Oracle SQL Developer and get the data back in 1 second. Or, if I switch to the select * form of the query, it works from the web app, too.

Any ideas? Is there something in particular I should be looking for? Could this somehow be a permissions issue?

Tim
Re: PLS-00382: expression is of wrong type [message #399976 is a reply to message #399973] Fri, 24 April 2009 13:40 Go to previous message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
Trolands,

Sorry.. I did not see the cursor definition earlier.

Since you have the cursor defined as hs_vrecipelist%ROWTYPE,

When you open the cursor, It should be ..


OPEN cv_products FOR
SELECT *
FROM hs_vrecipelist 
WHERE isinactive = 0 AND recipehierarchyid = p_categoryId
ORDER BY recipeshortdescription;



Thanks,
Rajesh.

[Updated on: Fri, 24 April 2009 13:41]

Report message to a moderator

Previous Topic: Finding duplicate in table
Next Topic: Trigger Help
Goto Forum:
  


Current Time: Mon Dec 05 11:05:00 CST 2016

Total time taken to generate the page: 0.10271 seconds