We help IT Professionals succeed at work.

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

superthumper
superthumper asked
on
28,054 Views
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') ???
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

This article is a little old but it is still valid
http://support.businessobjects.com/library/kbase/articles/c2001157.asp

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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 ...

http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch6.htm#i1211948

once there, go to
Example 6-15 Using REFCURSOR Variables in Stored Procedures
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.