SqlFunction

[pglezen]

I'm having trouble invoking an SQL function in Oracle using the Spring
SqlFunction wrapper. Here is a snippet from the JDBC invocation.

  call = conn.prepareCall("{? = call pkg_product.f_id_from_upc(?)}");
  call.registerOutParameter(1, OracleTypes.NUMERIC);
  call.setString(2, upcNo);
  call.execute();
  uniqueProductId = (long) call.getLong(1);			
  call.execute();

I've tried various combinations the SQL string and parameter declarations
to no avail. I was attempting to subclass SqlFunction with a constructor like this.

public MySqlFunction(DataSource ds) {
   super(ds, 
            "call pkg_product.f_id_from_upc(?)",
            new int[] {Types.VARCHAR}
            );
   compile();
}

But my invocation

MySqlFunction sf = new MySqlFunction(ds);
Object o = sf.runGeneric(new String[] {"xyz"});

I get the error

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [call pkg_product.f_id_from_upc(?)]; nested exception is java.sql.SQLException: ORA-06576: not a valid function or procedure name

But I'm pretty sure the function name is correct. If I add the leading question mark to the SQL statement:

? = call pkg_product.f_id_from_upc(?)

it will complain that I have not passed in enough parameters. But the first parameter would be an OUT parameter. I'm not sure how to do that with SqlFunction.

[trisberg]

SqlFunction is a wrapper class for a query that returns a single value. It is not intended to wrap a CallableStatement. Use the Stored Procedure class for that.

See http://static.springframework.org/sp...api/index.html for more info.

[pglezen]

Indeed, StoredProcedure seems to do the trick. The principal thing I missed was the

setFunction(true);

This, apparently, is what tells StoredProcedure to place the "? = call " before the name of the function when building the SQL string.