wood burning stoves 2.0*
The moose likes JDBC and the fly likes return values from functions into java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "return values from functions into java" Watch "return values from functions into java" New topic

return values from functions into java

joe weakers
Ranch Hand

Joined: May 31, 2004
Posts: 38
Hi there. I have created several pl/sql procedures and functions that I can run as standalone programs and I have no problem integrating them into my java code also. However I cannot access the return value of any created function from my java code. The functions appear to execute correctly because when I deliberately enter an invalid select statement I get the errors I expect to get for that instance nad if I enter a valid select statement in the function the program executes correctly but I still cannot access the function's return value. My java code where I execute the pl/sql function is as follows:

CallableStatement p = cont.conn.prepareCall("{call ? := Hellen.FUNC}");
p.setString(1, "z006"); //hellen.FUNC = function name

How do I access the return value of my function?
Do I need to get a ResultSet?

My function is as follows:

function func
return varchar
is tester varchar(4);
select cfcc_code into tester from hellen.test1 where test1.cfcc_code = 'A21';
return tester;

Any help will be gratefully received, Joe
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Joe,

You seem to be attempting to set the return value of the function to the String "z006" in the Java. That seems a bit of a strange thing to do.

You need to use:

To get the returned value you need the following:

I'd also question the wisdom of naming a function 'func'. This is obviously for testing purposes but when there are a number of layers involved it makes sense to use a name you can guarantee is not a keyword. MyFunc might be a better alternative to avoid the kind of error that will drive you insane.

joe weakers
Ranch Hand

Joined: May 31, 2004
Posts: 38
Cheers Jules. That is working perfectly now. Thanks a lot
I agree. Here's the link: http://aspose.com/file-tools
subject: return values from functions into java
Similar Threads
Execute pl/sql function which is having bind variables in java
Can a oracle Stored procedure return a cursor as a return value?
DBMS_SQL to create a view
calling pl/sql in Java
URGENT:::Can DML operation "insert" works inside a PL/SQL stored function ?