CallableStatement + registerOutParameter + multiple row result

Question

I've got a SQL statement of the form:

BEGIN\n 
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

To build upon what Luke Woodward answered and to refine my previous answer, you can create an Oracle type, use it to temporarily store data, and then return a sys_refcursor with your updates.

Create the new type:

CREATE OR REPLACE TYPE rowid_tab AS TABLE OF varchar2(30);

Create the database function:

CREATE OR REPLACE
FUNCTION update_tab
RETURN sys_refcursor
IS
    ref_cur sys_refcursor;
    v_tab rowid_tab;
BEGIN
    UPDATE tab 
    SET intA = intA+2
      , intB = intB*2
      , stringC = stringC||' more stuff.'
    RETURNING ROWID
    BULK COLLECT INTO v_tab;

    OPEN ref_cur FOR 
        WITH DATA AS (SELECT * FROM TABLE(v_tab))
        SELECT intA, intB, stringC
        FROM tab
        where rowid in (select * from data);
    RETURN ref_cur;
END;

Now, call the function in your java:

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

public class StructTest {    
    public static void main(String[] args) 
        throws Exception 
    {   
        System.out.println("Start...");

        ResultSet results = null;
        Connection c = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
        c.setAutoCommit(false);        

        String sql = "begin ? := update_tab(); end;";
        System.out.println("sql = "+sql);
        CallableStatement stmt = c.prepareCall(sql);        
        /* Register the out parameter. */
        System.out.println("register out param");
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        // get the result set
        stmt.execute();
        results = (ResultSet) stmt.getObject(1);
        while (results.next()){
            System.out.println("intA: "+results.getString(1)+", intB: "+results.getString(2)+", stringC: "+results.getString(3));
        }
        c.rollback();        
        c.close();    
    }
}

With my test data, I got the following results:

intA: 3, intB: 4, stringC: a more stuff.
intA: 6, intB: 10, stringC: C more stuff.
intA: 3, intB: 4, stringC: a more stuff.

Answer 2

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:\Users\Luke\stuff>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.