[Last Call] Learn how to a build a cloud-first strategyRegister Now


Oracle SQL - Call a stored procedure with a cursor out parameter

Posted on 2007-10-05
Medium Priority
Last Modified: 2013-12-19
I have a stored procedure I'm using to return a recordset with a cursor as an out parameter  to return a recordset.  I'm seeing some strange data when I bring the data into Crystal Reports.   In the SQL server world, I'd just go directly to Management Studio, open a 'new query' and execute the procedure... but in SQL server I don't need to use a cursor to return a recordset, I just 'DO' the select.  How see the results in 'Oracle SQL Developer' the same way I would in sql server management studio (or the old 'query analyzer') ???
Question by:superthumper
LVL 101

Expert Comment

ID: 20022841
Oracle requires a ref cursor as the first parameter returned to the report.

This article is a little old but it is still valid

LVL 101

Expert Comment

ID: 20022858
LVL 18

Accepted Solution

sventhan earned 800 total points
ID: 20022925
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

ID: 20024436
Does it really have to be that much more complicated with Oracle than SQL Server?  I just want to see if my procedure works and returns sensical data.
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 20024624
Oracle PL\SQL stored procedures by default *DO NOT* return record sets!

I have learned that people with SQL Server experience expect this functionality, but Oracle historically did not provide this via PL\SQL.  That may be because the standard Oracle client tools (Oracle Forms and Oracle Reports) built their own record sets internally - they did not depend on PL\SQL for this.  In recent versions of Oracle, as Oracle has tried to make their database and software get along better with non-Oracle software, support for "ref cursors", arrays, collections. etc has been added to PL\SQL.  But getting a "record set" out of a stored procedure may still be an easier task in SQL Server than in Oracle.  Now, getting a stored procedure to perform efficiently and scale well to large numbers of users may be a much easier task in Oracle than in SQL Server.

Both systems have things they do very well, but be aware that they are very different systems, and their strengths and weaknesses are different.

Expert Comment

ID: 20034940
If you are just wanting to run a procedure which returns a ref cursor and you want to see the content of that ref cursor, you can do that in SQLPlus.  Here is how ...


once there, go to
Example 6-15 Using REFCURSOR Variables in Stored Procedures
LVL 18

Assisted Solution

sventhan earned 800 total points
ID: 20034990
if you have Toad that would even easier to see the ref_cursor results.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question