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.

score 1 · Answer 1 · answered Feb 05 '20 at 05:55

1

You can get the connection under jdbcTemplate and get output using get methods as getNString

try (Connection connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
CallableStatement statement = connection.prepareCall("{call myprocedure(?,?,?,?,?,?,?,?)}");
statement.execute();
statement.getNString(1); // using index or your parameter name

Retrieves the value of the designated NCHAR, NVARCHAR or LONGNVARCHAR parameter as a String in the Java programming language.

answered Feb 05 '20 at 05:55

Ori Marko

56,308
23
131
233

It's pure jdbc. – Alex78191 Feb 12 '20 at 16:36
call `statement.registerOutParameter(1, Types.NVARCHAR)` is missing – Alex78191 Feb 12 '20 at 16:38

Alex78191 · Accepted Answer · 2020-02-15T17:43:40.503

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.

score 0 · Answer 3 · answered Feb 04 '20 at 16:01

I found some guidance from an older question here, and came up with this monstrosity:

    public Map<String,Object> callMyProcedure() {
        return jdbcTemplate.call(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                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);
                return cs;
            }
        },Arrays.asList(
                new SqlParameter(Types.VARCHAR),
                new SqlParameter(Types.VARCHAR),
                new SqlParameter(Types.VARCHAR),
                new SqlParameter(Types.VARCHAR),
                new SqlParameter(Types.VARCHAR),
                new SqlParameter(Types.VARCHAR),
                new SqlOutParameter("errorCode",Types.VARCHAR),
                new SqlOutParameter("errorMessage",Types.VARCHAR)
        ));
    }

It does work, but I'm looking for an answer that can do the same thing more succinctly. Maybe Spring has added a new interface to JdbcTemplate in the years since that older answer?

Yes, it's the answer https://stackoverflow.com/a/48821425. Isn't it easier to use pure jdbc? — Alex78191, Feb 12 '20 at 16:45
@Alex78191 I don't know where `jdbcTemplate` ends and "pure JDBC" begins, but the main reasons I don't like this answer are that it requires lots of code that provides no information (i.e. re-stating the data types of the input parameters), is hard for a programmer to read, and would be prone to bugs from all the code duplication and copy-pasting needed. If I could *just* register the output arguments and not all the input arguments, it would go a long way toward more readable, maintainable code. — workerjoe, Feb 12 '20 at 17:02
Pure jdbc means using `cs.execute` instead of `jdbcTemplate.call`. — Alex78191, Feb 12 '20 at 17:22
@Alex78191 Can you add an answer, or link to one, that shows how to do this with pure JDBC? — workerjoe, Feb 14 '20 at 20:11
https://stackoverflow.com/a/40984167/ `1. Using CallableStatement:` — Alex78191, Feb 15 '20 at 17:30

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

3 Answers3