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.
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.
Joined: Sep 01, 2005
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.
Joined: Nov 04, 2009
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.
subject: Can a oracle Stored procedure return a cursor as a return value?