File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
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
I agree. Here's the link:
subject: return values from functions into java
It's not a secret anymore!