File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Can a oracle Stored procedure return a cursor as a return value? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can a oracle Stored procedure return a cursor as a return value?" Watch "Can a oracle Stored procedure return a cursor as a return value?" New topic
Author

Can a oracle Stored procedure return a cursor as a return value?

Kalyana Chakravathy
Greenhorn

Joined: Nov 04, 2009
Posts: 8
Hi,
I have a doubt on Oracle Stored Procedures and Functions. What is difference between the two and can a stored procedure return a value back. Some one at my place wrote a stored procedure that he says returns a cursor.
I wrote code in java using Stored Procedure in spring. But it always gives me a DB Exception.

It seems it thinks the procedure takes two parameters when I don't say its a function. I see in debug it takes the SqlOutParameter also as a stored procedure parameter. When I declare it to be a function its not, its seeing only SqlParameter declarations and changing.

It takes only one parameter.

If i change it to function in my java code, it says there is no function in scope, and there is none.

Thats when I got the doubt, can stored procedures return values.

Please answer anyone.

I had another thread with question on how to access stored procedure, but no one is answering it.

Thanks,
Kalyana CJ.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Kalyana,

Can you post pl/sql code?

Regards,

Fatih.
Kalyana Chakravathy
Greenhorn

Joined: Nov 04, 2009
Posts: 8
The procedure.



The java code,



This fails with the DB exception,



If I say


then it fails saying,



and it should fail as there is no such function. But the number of arguments that the stored procedure expects and the function expects have changed.

Thats why I got the doubt may be stored procedure doesn't return any value.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Kalyana,

This procedure only takes one parameter and does not have any out parameter, so it does not return anything and parameter count is also wrong.

First correct your pl/sql code, this sample code from oracle-base will be helpful, and this is also.

Regards,

Fatih.
Kalyana Chakravathy
Greenhorn

Joined: Nov 04, 2009
Posts: 8
Thank you very much, it works now. By the way if I convert it into a function will there be any problem. I read that stored procedure is compiled only once and stored, where as a function is compiled every time. Is that true, if so how much does it affect the performance. I am very new to JDBC, hibernate, Stored Procedures and stuff. All I know is just some basic knowledge of SQL queries. Please correct me if I am wrong.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can a oracle Stored procedure return a cursor as a return value?