Calling Oracle functions via SimpleJdbcCall and related tools is not always obvious. In this article we will take a look at:
- Calling a simple pass through function.
- Calling a synonym of a function located in a different schema.
- Calling a function with boolean parameter.
Calling a simple pass through function
Let's have a function which reads a string parameter and returns it as a result.
CREATE FUNCTION pass_through_fun(text_arg VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN 'passing ' || text_arg;
END;
/
In this case we can use the SimpleJdbcCall and its ExecuteFunction() method.
A SQL statement we will use will have to have two bindings, one for the return value and second for the parameter. The return parameter is not named, so we have to declare some result parameter, while ensuring the result parameter is first in the list and other parameters are in correct order.
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("pass_through_fun")
.declareParameters(
new SqlOutParameter("result", Types.VARCHAR),
new SqlParameter("text_arg", Types.VARCHAR)
);
String result = call.executeFunction(String.class, "value");
System.out.println("result: " + result);
Calling a function synonym
If a function is located in another schema and only a synonym is available in local schema, we have to disable Spring's metadata resolution via the SimpleJdbcCall.withoutProcedureColumnMetaDataAccess() method.
The reason is, Spring resolves metadata of a called objects from all_procedures and all_arguments tables. While doing so, it checks whether owner of procedure or argument is equal to the current schema which is resolved from connection, e.g. all_arguments.owner = 'local_schema_name' AND all_procedures.owner = 'local_schema_name'.
Because the real owner is located elsewhere, the metadata resolution fails on the SQLException: Missing IN or OUT parameter at index:: 1 error.
The JDBC call wit resolution disabled:
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("pass_through_fun")
.withoutProcedureColumnMetaDataAccess()
.declareParameters(
new SqlOutParameter("result", Types.VARCHAR),
new SqlParameter("text_arg", Types.VARCHAR)
);
Calling a function with boolean parameter
The Oracle JDBC drivers does not support boolean type for PLSQL stored procedures.
JDBC calls passing boolean parameters usually fails with the PLS-00306: wrong number or types of arguments in call to... error.
This issue is usually solved by wrapping the function into another function with an integer parameter instead, leaving the int -> bool transformation to the wrapper.
The SimpleJdbcCall does not offer any tools for wrapping the parameter. Fortunately, we can use some lower level Simple JDBC tools to create required statement, without having to fall back to raw JDBC calls (e.g. creating a CallableStatement from connection).
Let's have a function with boolean parameter:
CREATE FUNCTION boolean_arg_fun(bool_arg BOOLEAN) RETURN VARCHAR2 AS
BEGIN
IF bool_arg THEN
RETURN 'yes';
ELSE
RETURN 'no';
END IF;
END;
/
We can handcraft a statement, which wraps boolean into integer by using sys.diutil.int_to_bool().
String statement = "{? = call boolean_arg_fun(sys.diutil.int_to_bool(?))}";
List<SqlParameter> params = List.of(
new SqlOutParameter("result", Types.VARCHAR),
new SqlParameter("bool_arg", Types.BOOLEAN)
);
Map<String, Object> parameters = Map.of(
"bool_arg", true
);
CallableStatementCreatorFactory factory = new CallableStatementCreatorFactory(statement, params);
CallableStatementCreator creator = factory.newCallableStatementCreator(parameters);
Map<String, Object> result = jdbcTemplate.call(creator, params);
result.forEach((name, value) -> System.out.printf("%s: %s%n", name, value));
Calling a function with multiple IN and OUT parameters
Now, we can combine previous knowledge into more complex example.
Let's have a function with multiple IN and OUT parameters:
CREATE FUNCTION multiple_args_fun(
text_in_arg IN VARCHAR2,
text_out_arg OUT NOCOPY VARCHAR2,
clob_in_arg IN CLOB,
clob_out_arg OUT NOCOPY CLOB,
bool_in_arg IN BOOLEAN DEFAULT FALSE
) RETURN VARCHAR2 AS
BEGIN
text_out_arg := 'passing ' || text_in_arg;
clob_out_arg := TO_CLOB('passing ' || dbms_lob.substr( clob_in_arg, 100, 1 ));
IF bool_in_arg THEN
RETURN 'yes';
ELSE
RETURN 'no';
END IF;
END;
/
And a Java call:
String statement = "{? = call multiple_args_fun(?, ?, ?, ?, sys.diutil.int_to_bool(?))}";
List<SqlParameter> params = List.of(
new SqlOutParameter("result", Types.VARCHAR),
new SqlParameter("text_in_arg", Types.VARCHAR),
new SqlOutParameter("text_out_arg", Types.VARCHAR),
new SqlParameter("clob_in_arg", Types.CLOB),
new SqlOutParameter("clob_out_arg", Types.CLOB),
new SqlParameter("bool_in_arg", Types.BOOLEAN)
);
Map<String, Object> parameters = Map.of(
"text_in_arg", "text value",
"clob_in_arg", "clob value",
"bool_in_arg", true
);
CallableStatementCreatorFactory factory = new CallableStatementCreatorFactory(statement, params);
CallableStatementCreator creator = factory.newCallableStatementCreator(parameters);
Map<String, Object> result = jdbcTemplate.call(creator, params);
result.forEach((name, value) -> {
Object converted = value;
if (value instanceof Clob clob) {
try {
converted = new String(clob.getAsciiStream().readAllBytes());
} catch (IOException | SQLException e) {
throw new RuntimeException(e);
}
}
System.out.printf("%s: %s%n", name, converted);
});
Passing CLOB value
To pass a CLOB value into the function, you have to create java.sql.Clob object byt the same database connection, by which the subsequent function call is made. Otherwise ORA-22922: nonexistent LOB value will be returned by the database.
The connection can be obtained form a datasource. To ensure the same connection is returned, the code has to be executed in a transaction (e.g. TransactionTemplate.execute()) which binds the connection to the current thread. Otherwise datasource returns a new connection, for each get connection call.
var contact = "clob content";
var dataSource = requireNonNull(jdbcTemplate.getDataSource(), "Data source is not configured!");
var connection = DataSourceUtils.getConnection(dataSource);
var clob = connection.createClob();
try (var writer = clob.setCharacterStream(1)) {
writer.write(content);
}