aspose file tools
The moose likes JDBC and Relational Databases 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 OCA Java SE 8 Programmer I Study Guide 1Z0-808 this week in the OCAJP forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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
It is sorta covered in the JavaRanch Style Guide.
subject: return values from functions into java