return values from functions into java

[joe weakers]

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
p.execute();

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);
BEGIN
select cfcc_code into tester from hellen.test1 where test1.cfcc_code = 'A21';
return tester;
END;

Any help will be gratefully received, Joe

[Julian Kennedy]

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:

cs.registerOutParameter(1, java.sql.Types.VARCHAR);

To get the returned value you need the following:

String myString = cs.getString(1);

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.

Jules

[joe weakers]

Cheers Jules. That is working perfectly now. Thanks a lot