How add OUT parameter to function in Java JDBC (Oracle database)

Stack Over Source

SQL function in Oracle Database:

FUNCTION init(id in number, code out varchar2) RETURN number;

I have SQL (Oracle database) in my java code:

private static final String MY_FUNCTION_SQL = "SELECT live.api.init(?,?) FROM DUAL";

And my method:

void myMethod() throws SQLException {
  try(CallableStatement cs = sdcon.prepareCall(MY_FUNCTION_SQL) {
    cs.setLong(1, _myID);
    cs.registerOutParameter(2, Types.VARCHAR);
    ResultSet resultSet = sdInsertTask.executeQuery();
  }
}

After executeQuery() I got exception:

java.sql.SQLException: ORA-06572: Function INIT has out arguments.

javasqloraclejdbc

Answers

answered 6 months ago R. Mosser #1

change the my_function_sql to

private static final String MY_FUNCTION_SQL = "call live.api.init(?,?)";

answered 6 months ago AxelH #2

You can find the syntax in the javadoc of CallableStatement :

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}

So you just need to adapt your query, since you need the OUT parameter, use the first syntax

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

Something like :

String initQuery = "{?= call live.api.init(?)}";
long _myID = 1L;
try(CallableStatement cs = conn.prepareCall(initQuery)) {
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.setLong(2, _myID);

    ResultSet resultSet = cs.execute();
}

Note: you called another Statement called sdInsertTask in your code... instead of cs

You can find a complete example of this here

comments powered by Disqus