Help executing callable query

[imilina]

Hello,

Can anybody help me execute following callable query:

BEGIN
	? := case MY_FUNC(?, ?) when true then '1' else '0' end;
END;

I'm using oracle database, and MY_FUNC function is returning boolean, so this is my attempt to workaround oracle jdbc driver's inability to work with booleans (creating wrapper function is currently not an option). Query executes ok when run in my sql console...

I tried the following but it fails with message: java.sql.SQLException: Invalid SQL type.

List params = new ArrayList();
params.add(new SqlOutParameter("out", Types.VARCHAR));

Map results = getJdbcTemplate().call(new CallableStatementCreator() {
	public CallableStatement createCallableStatement(Connection connection) throws SQLException {
		CallableStatement cs = connection.prepareCall("? := case MY_FUNC(?, ?) when true then '1' else '0' end");
		cs.registerOutParameter(1, java.sql.Types.VARCHAR);
		cs.setString(2, "param1");
		cs.setString(3, "param2");
		return cs;
	}
}, params);

I expect that I should be able to execute this callable query using jdbcTemplate, but I don't have much knowledge on how to do this. Please help.

Regards,
Igor.

[trisberg]

I think you are close to a solution. Try:

CallableStatement cs = connection.prepareCall("begin ? := case MY_FUNC(?, ?) when true then '1' else '0' end; end;");

[trisberg]

Or, you could use this syntax:

"{ ? = call case MY_FUNC(?, ?) when true then '1' else '0' end }"

which I think is a bit "prettier"

[imilina]

Oh, thank you very much, that worked like a charm My head hurts a little less now.

Regards,
Igor.