Callablestatement + Registeroutparameter + Multiple Row Result

CallableStatement + registerOutParameter + multiple row result

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.

What is the meaning and use of registerOutParameter in callable statements

Your question is very tight to how stored procedures are executed in an RDBMS. Note that the execution of a stored procedure through a CallableStatement is very different from the execution of a SELECT query which returns a ResultSet. When executing such a query the RDBMS will return a description of the columns and the driver uses this description to know what to expect for the data.

Before executing a stored procedure, the JDBC specification says that you must call registerOutParameter for each OUT parameter. This is to indicate to the driver what data it should expect. Why? Because for many RDBMSs there is no way to describe a stored procedure. It's up to the user to define the out parameters through this API. The driver wouldn't be able to figure it out on its own. You can then execute the stored procedure and then call the getters on the CallableStatement to get the value of the OUT parameters that you have registered.

How do you get multiple resultset from a single CallableStatement?

I found this great article. http://www.herongyang.com/JDBC/MySQL-CallableStatement-Multiple-ResulSet.html

Here is the code from that article.

/**
 * MySqlCallMultipleResultSet.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class MySqlCallMultipleResultSet {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds 
        = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// Create CallableStatement
      CallableStatement cs = con.prepareCall("CALL HeadTail(?)");

// Register OUT parameters
      cs.registerOutParameter(1, java.sql.Types.INTEGER);     

// Execute the CALL statement and expecting multiple result sets
      boolean isResultSet = cs.execute();

// First ReulstSet object
      if (!isResultSet) {
        System.out.println("The first result is not a ResultSet.");
        return;
      }

// First ReulstSet object
      System.out.println("Head of the table:");
      ResultSet res = cs.getResultSet();
      while (res.next()) {
        System.out.println("  "+res.getInt("ID")
          +", "+res.getString("FirstName")
          +", "+res.getString("LastName")
          +", "+res.getTimestamp("ModTime"));

      }
      res.close();

// Move to the next result
      isResultSet = cs.getMoreResults();
      if (!isResultSet) {
        System.out.println("The next result is not a ResultSet.");
        return;
      }

// Second ReulstSet object
      System.out.println("Tail of the table:");
      res = cs.getResultSet();
      while (res.next()) {
        System.out.println("  "+res.getInt("ID")
          +", "+res.getString("FirstName")
          +", "+res.getString("LastName")
          +", "+res.getTimestamp("ModTime"));

      }
      res.close();

// Retrieve OUT parameters
      System.out.println("Total number of records: "+cs.getInt(1));

// Close resource
      cs.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}

Callable Statement - PostgreSQL - Multiple Out Parameters

I'm testing the procedure call with multiple OUT parameter in JDBC with PostgreSQL 14.1 and driver 42.2.20.

Some care must be taken, as the ANSI call fails

cn.prepareCall("{call proc1(?,?,?,?)}")

with org.postgresql.util.PSQLException: ERROR: proc1(character varying, integer) is a procedure Hint: To call a procedure, use CALL. This may be connected with this answer

Similar fails also the PostgreSQL CALL cn.prepareCall("call proc1(?,?,?,?)") with

Caught: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type integer: "null". This suggest a problem with the nullinteger OUTparamater.

I finally get it with a bit hack defining the integer parameter as INOUT and passing zero.

Procedure

create or replace PROCEDURE proc1(input1 IN varchar(10), input2 IN integer, output1 INOUT integer, output2 OUT varchar(10))
LANGUAGE plpgsql
AS $$
BEGIN
output2 := input1;
output1 := input2;
END;
$$;

JDBC

// procedure call with two OUT parameter 
stmt = cn.prepareCall("call proc1(?,?,?,?)") 
stmt.setString(1,'x')
stmt.setInt(2,100)
stmt.setInt(3,0)
stmt.registerOutParameter(3,Types.INTEGER)
stmt.registerOutParameter(4,Types.VARCHAR)
stmt.execute()
println  stmt.getInt(3)
println  stmt.getString(4)

returns as expected

100
x

Fetch pl/sql array return values in java

I didn't do that with ARRAY but it should works. First you must register out parameter of your function. So it can be like this.

private final String PRODECURE_NAME = "{? = call <ProcedureName>(?,?,?)}";

Connection con = null;
CallableStatement cs = null;

       try {
            con = DAOFactory.getDatabaseConnection();
            cs = con.prepareCall(PRODECURE_NAME);
            cs.registerOutParameter(1, java.sql.Types.ARRAY);
            cs.setYourType(2, <yourData>);
            cs.setYourType(3, <yourData>);
            cs.setYourType(4, <yourData>);
            cs.execute();
            Array arr = cs.getArray(1);
            if (arr != null) {
               String[] data = (String[]) arr.getArray();
            }
        } 
        catch (SQLException ex) {
            Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
            try {
                con.rollback();
            }
        }
        finally {
            if (con != null) {
                try {
                    con.close();
                } 
                catch (SQLException ex) {
                    Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

Try this man and give me then asnwer wether it do or not.

EDIT:

These char ? represents one parameter that you will set(it's named as parametrized).
So this:

cs.setYourType(2, <yourData>);
cs.setYourType(3, <yourData>);
cs.setYourType(4, <yourData>);

means, that you set your three parameters (?), first parameter of method is column index and second are you data of your specific type.

---

EDIT 2:

So sorry i wrote bad solution, already updated so check code now and try it.

---

Related Topics