If your intention is to return a REF CURSOR, use the predefined type sys_refcursor, as shown below.
Main Topics
Browse All TopicsI am very new to Oracle stored procedures. I am trying to return a recordset from my Oracle database using a SQL query (inside a stored procedure) with one input parameter. I understand I need to use a ref cursor, but I am having difficulty with compiling code based on examples I have seen here and other places on the web.
The concept is simple, I have a table of counties and there are 4 regions in the state. When the user submits a region (region_param), the stored procedure should return a list of counties. Eventually, this stored procedure will serve an XML-based web service and populate drop-downs, etc. I want to crawl before I run, though. I will ask how to return this to a web service once I get stored procedures figured out.
The error code I keep getting is "TYPES.cursorType must be declared." Here is the code I have so far...any help on syntax is appreciated!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks for the quick responses. sujith80, using your SQL the procedure compiled properly, but I get the following error when I try to execute it and submit a region number:
"EUnsupportedParamD
kretzschmar, I think you are right, I need to declare the cursor type. I have a hard time building the cursor type, though. I am using Oracle Maestro, is there a SQL syntax I could use? When I try to create the parameter using the dialog boxes, I don't get cursor or anything similar as a "base type." I am going to download TOAD and see if that is any different.
One other possible issue once we get this running correctly, is the code (as written) enough to return all of the values or is there loop/end code I need to write to output all of the counties? I need to write it so that it is used by a web service called by a remote server.
Thanks again.
I have an update, disregard my last post. Thanks to your help I have the procedure successfully compiled. I needed to declare the package with the cursor type. I think this is working now, but I need help calling the procedure and returning a recordset when I pass it a region number.
If you have any advice/thoughts on the best way for this to be used by a web service (i.e., to have the service return an XML document or an array), I would greatly appreciate it!
Thanks again!
>> I needed to declare the package with the cursor type.
>> TYPE varCUR IS REF CURSOR;
This is an old school approach used in Oracle Versions 8 and below. This usage pushes you to create a package just for the sake of creating a ref cursor type. Now oracle has a predefined ref cursor type called sys_refcursor. This is JUST same as the explicit declaration you have made above.
Here is a sample code detailing how to use a procedure with ref cursor OUT parameter.
Business Accounts
Answer for Membership
by: kretzschmarPosted on 2008-07-07 at 23:25:14ID: 21951335
do you have a package named TYPES and is there declared the type cursorType?
meikl ;-)