How to succinctly call a PL/SQL procedure and return its output variables in Spring with JdbcTemplate?

Question

New to Oracle here. My experience is building web apps which send queries to a database and get a "result set" back, for example in Java with Spring and JdbcTemplate.

For example, here's a code sample with a regular SQL query, using a RowMapper class to turn the rows of a result set into a Java list:

public List<Widget> findAllWidgets() {
    return jdbcTemplate.query("SELECT * FROM widgets", new WidgetRowMapper());
}

Now I am tasked with writing a query that calls a PL/SQL stored procedure. This procedure has two input arguments (these I can handle) and two output arguments (let's call them error_code and error_message). I want to send a query to the database that will (a) run the procedure with my inputs and (b) return the two outputs. I would be equally happy to have the two outputs as a "row" or simply bind them to two Java variables.

Here's what I've tried, and it's not throwing an error but it's not getting the output values, either. The Java variables errorCode and errorMessage remain empty strings:

public Map<String,String> callMyProcedure() {
    String errorCode="";
    String errorMessage="";
    jdbcTemplate.update("call myprocedure(?,?,?,?)","input1","input2",errorCode,errorMessage);
    return Map.of("errorCode",errorCode,"errorMessage",errorMessage);
}

The question is: How can I capture the values of the PL/SQL procedure's "OUT" variables when calling the procedure from Java with JdbcTemplate?

EDIT: I accepted Alex's answer which doesn't use JdbcTemplate, because it seems to be the better way. My own answer does use JdbcTemplate but takes a lot more code, so if you're searching for something that specifically answers the question, that will do it.

Answer 1

You can use plain JDBC.

final String charlie;
final String zulu;
try (CallableStatement cs = connection.prepareCall("{call myprocedure(?,?,?,?,?,?,?,?)}")) {
    cs.setString(1, "foo");
    cs.setString(2, "bar");
    cs.setString(3, "baz");
    cs.setString(4, "whisky");
    cs.setString(5, "tango");
    cs.setString(6, "foxtrot");
    cs.registerOutParameter(7, Types.VARCHAR);
    cs.registerOutParameter(8, Types.VARCHAR);
    cs.execute();
    connection.commit(); // optional
    charlie = cs.getString(7);
    zulu = cs.getString(8);
}

When using JDBC, it is dangerous to use the getInt method and similar ones, since they convert the type to primitive and zero is replaced by 0. It is better to use a (Integer) cs.getObject(). Similarly, setInt does not support the reference type.