Function x is not a procedure or is undefined

Question

I have a function that receives 2 string inputs and returns a string output, let's call it 'x', I'm trying to call that function on a jsp website, I've done:

String jobquery = "{call x(?, ?)}";
CallableStatement callStmt = conn.prepareCall(jobquery);

callStmt.registerOutParameter(1, OracleTypes.NVARCHAR);
callStmt.setString(1, "hello");
callStmt.setString(2, "world");
callStmt.execute();

Which gives this error:

java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'x' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

And I'm like, yeah I know, it's not a procedure, it's a function! But why does it think that it's a procedure? And how should I run my code?

Answer 1

You're getting that error because you are attempting to call a procedure rather than a function, because you haven't indicated any return argument.

You need a placeholder for the return value; your parameter numbers need to be consistent too:

String jobquery = "{ ?=call x(?, ?) }";
CallableStatement callStmt = conn.prepareCall(jobquery);

callStmt.registerOutParameter(1, OracleTypes.NVARCHAR);
callStmt.setString(2, "hello");
callStmt.setString(3, "world");
callStmt.execute();