sql - CallableStatement + registerOutParameter + multiple row result

Question

I've got a SQL statement of the form:

BEGIN
 
UPDATE tab 
SET stuff
WHERE stuff
RETURNING intA, intB, stringC
INTO ?,?,?

I've registered the appropriate Out parameters.

Here's where I have some questions: Do I call stmt.executeQuery() or stmt.execute()? Further, I know with a normal SELECT query I can loop through the resultSet and populate my object -- what's the equivalent for multiple rows of Out parameters?

EDIT: Perhaps I can register a single out parameter of type CURSOR and loop over this result.

EDIT2: Could I potentially have multiple resultSet's that I need to loop over? Thanks!

Answer 1

I believe you can achieve what you are looking for, but you will need to handle PL/SQL arrays rather than cursors or result sets. Below is a demonstration.

I have a table, called TEST, with the following structure:

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------

 A                                                  NUMBER(38)
 B                                                  NUMBER(38)
 C                                                  NUMBER(38)

and containing the following data:

SQL> select * from test;

         A          B          C
---------- ---------- ----------
         1          2          3
         4          5          6
         7          8          9

I need to create an array type for each type of column used. Here, I only have NUMBERs, but if you have one or more VARCHAR2 columns as well, you'll need to create a type for those too.

SQL> create type t_integer_array as table of integer;
  2  /

Type created.

The table and any necessary types are all we need to set up in the database. Once we've done that, we can write a short Java class that does an UPDATE ... RETURNING ..., returning multiple values to Java:

import java.math.BigDecimal;
import java.util.Arrays;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;

public class UpdateWithBulkReturning {
    public static void main(String[] args) throws Exception {
        Connection c = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE", "user", "password");

        c.setAutoCommit(false);

        /* You need BULK COLLECT in order to return multiple rows. */
        String sql = "BEGIN UPDATE test SET a = a + 10 WHERE b <> 5 " +
                     "RETURNING a, b, c BULK COLLECT INTO ?, ?, ?; END;";

        CallableStatement stmt = c.prepareCall(sql);

        /* Register the out parameters.  Note that the third parameter gives
         * the name of the corresponding array type. */
        for (int i = 1; i <= 3; ++i) {
            stmt.registerOutParameter(i, Types.ARRAY, "T_INTEGER_ARRAY");
        }

        /* Use stmt.execute(), not stmt.executeQuery(). */
        stmt.execute();

        for (int i = 1; i <= 3; ++i) {
            /* stmt.getArray(i) returns a java.sql.Array for the output parameter in
             * position i.  The getArray() method returns the data within this
             * java.sql.Array object as a Java array.  In this case, Oracle converts
             * T_INTEGER_ARRAY into a Java BigDecimal array. */
            BigDecimal[] nums = (BigDecimal[]) (stmt.getArray(i).getArray());
            System.out.println(Arrays.toString(nums));
        }

        stmt.close();
        c.rollback();
        c.close();
    }
}

When I run this, I get the following output:

C:UsersLukestuff>java UpdateWithBulkReturning
[11, 17]
[2, 8]
[3, 9]

The outputs displayed are the values returned from the columns A, B and C respectively. There are only two values for each column since we filtered out the row with B equal to 5.

You might want the values grouped by row instead of grouped by column. In other words, you might want the output to contain [11, 2, 3] and [17, 8, 9] instead. If that's what you want, I'm afraid you'll need to do that part yourself.