Using cursors and getting result in Oracle PL/SQL with Java/JDBC

Question

I have a PL/SQL query constructed like this :

DECLARE
a NUMBER;
B NUMBER;
CURSOR cursor
IS
 ( SOME SELECT QUERY);
BEGIN
  OPEN cursor;
    LOOP
    SOME STUFF;
    END LOOP;
  CLOSE cursor;
END

How can I run this query from a java code using jdbc and get the resultset? I have tried running the query without using cursor, and its running correctly. I couldn't figure out a way to do this in java code. If I run the query directly onto oracle client, it works with no problems. So there is no problem with the query.

P.S. I dont want to store the code as stored procedure and call that due to some constraints.

Answer 1

The other answers here seem super complicated.

Using SYS_REFCURSOR

Since forever, you could retrieve SYS_REFCURSOR types very easily from JDBC:

DECLARE
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR SELECT ...;
  ? := cur;
END;

Now run the above from Java like this:

try (CallableStatement c = con.prepareCall(sql)) {
    c.registerOutParameter(1, OracleTypes.CURSOR); // -10
    c.execute();

    try (ResultSet rs = (ResultSet) c.getObject(1)) {
        ...
    }
}

Of course, you can also declare your own cursors in packages as suggested by pmr's answer, but why would you if you're running an anonymous block from JDBC?

Using Oracle 12c implicit result sets

Oracle 12c added a handy new feature for these cases, which resembles the way SQL Server / Sybase and MySQL think about procedures / batches that return results. You can now use the DBMS_SQL.RETURN_RESULT procedure on any cursor, which returns it "by magic":

DECLARE
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR SELECT ...;
  DBMS_SQL.RETURN_RESULT(cur);
END;

Due to a bug (or "feature") in the Oracle JDBC driver, it's a bit more tricky to fetch that cursor correctly from JDBC but it can certainly be done as I've shown in this article here. This is how you can discover any number of implicit cursors from any anonymous PL/SQL block and/or procedure, trigger, etc...:

try (PreparedStatement s = cn.prepareStatement(sql)) {
    // Use good old three-valued boolean logic
    Boolean result = s.execute();

    fetchLoop:
    for (int i = 0;; i++) {

        // Check for more results if not already done in this iteration
        if (i > 0 && result == null)
            result = s.getMoreResults();
        System.out.println(result);

        if (result) {
            result = null;

            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
            }
            catch (SQLException e) {
                // Ignore ORA-17283: No resultset available
                if (e.getErrorCode() == 17283)
                    continue fetchLoop;
                else
                    throw e;
            }
        }
        else if (s.getUpdateCount() == -1)
            // Ignore -1 value if there is one more result!
            if (result = s.getMoreResults())
                continue fetchLoop;
            else
                break fetchLoop;
    }
}

Using jOOQ

In case you're using jOOQ already in your application to call stored procedures, you could avoid the above JDBC loop and use DSLContext.fetchMany() in order to easily fetch all the cursors into one data structure:

for (Result<?> result : ctx.fetchMany(sql))
  for (Record record : result)
    System.out.println(record);

Or, use code generation in case this is from a procedure, instead of an anonymous block. Your procedure will then give access to these results using procedure.getResults()

Answer 2

Because the signature of java.sql.PreparedStatement.execute() is "boolean execute()" not "Boolean execute()", "result" variable could never be null as the consequence of boxing the returned value of the s.execute(), so the test "i>0 && result==null" could be "result==null"

Answer 3

@Rajat,

Could you try below method:

To retrieve the cursor you should declare it as a REF CURSOR in Package spec.

  --Creating the REF CURSOR type
  type g_cursor is ref cursor;

In both, spec and body, you need declare an out REF CURSOR variable in procedure signature, how cited above.

  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor);

The cursor must be opened in procedure’s body to return, this way:

open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

The complete Package:

create or replace package PAC_CURSOR is
  --Creating REF CURSOR type
  type g_cursor is ref cursor;

  --Procedure that return the cursor
  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor); -- Our cursor

end PAC_CURSOR;
/

create or replace package body PAC_CURSOR is
  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor) is

       begin
        --Opening the cursor to return matched rows
        open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

  end PRO_RETURN_CARS;

end PAC_CURSOR;

We have Oracle side ready, now we need create Java call

How the cursors are being returned by a procedure, we’ll used a java.sql.CallableStatement instance:

CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");

The registerOutParameter will obtain oracle.jdbc.OracleTypes.CURSOR type and return a java.sql.ResultSet instance. We can iterate the ResultSet like a common Iterator.

Each row column returned by SELECT will be represented how a map, using correspondent getter. For example, we will call getString() method when value of column is a varchar, getDate() when is a date and etc.

The complete code will be like this:

//Calling Oracle procedure
CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");

//Defining type of return
cs.registerOutParameter("o_cursor", OracleTypes.CURSOR);
cs.setLong("i_id", id);

cs.execute();//Running the call

//Retrieving the cursor as ResultSet
ResultSet rs = (ResultSet)cs.getObject("o_cursor");

//Iterating the returned rows
while(rs.next()){
    //Getting column values
    System.out.println("ID: " + rs.getLong("car_id"));
    System.out.println("Manufacturer: " + rs.getString("company"));
    System.out.println("Model: " + rs.getString("model"));
    System.out.println("Color: " + rs.getString("color"));
    System.out.println("HP: " + rs.getString("hp"));
    System.out.println("Price: " + rs.getFloat("price"));
}

In the end you will get any value returned in a SELECT clause.

Answer 4

This is not possible. You cannot return a result set from an anonymous PL/SQL block (and therefor there is no way to get it from JDBC).

You will need to run the select directly from JDBC.

The only, really ugly workaround would be to use dbms_output.put_line() and the read that afterwards. But that is a really ugly hack and processing the result of the SELECT query directly in JDBC is much better.

---

Edit 1

Here is a little example using dbms_output:

Connection con = ....;

// turn on support for dbms_output
CallableStatement cstmt = con.prepareCall("{call dbms_output.enable(32000) }");
cstmt.execute();

// run your PL/SQL block
Statement stmt = con.createStatement();
String sql =
    "declare  \n" +
    " a number;  \n" +
    " cursor c1 is select id from foo;  \n" +
    "begin  \n" +
    "  open c1; \n" +
    "  loop \n" +
    "    fetch c1 into a;  \n" +
    "    exit when c1%notfound;  \n" +
    "    dbms_output.put_line('ID: '||to_char(a)); \n" +
    "  end loop; \n" +
    "end;";
stmt.execute(sql);

// retrieve the messages written with dbms_output
cstmt = con.prepareCall("{call dbms_output.get_line(?,?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.registerOutParameter(2,java.sql.Types.NUMERIC);

int status = 0;
while (status == 0)
{
    cstmt.execute();
    String line = cstmt.getString(1);
    status = cstmt.getInt(2);
    if (line != null && status == 0)
    {
        System.out.println(line);
    }
}

---

Edit 2 (this is too long for a comment)

Nesting loops to retrieve data is almost always a bad idea. If you find your self doing something like this:

begin
  for data_1 in (select id from foo_1) loop
    dbms_output.put_line(to_char(data_1.id));

    for data_2 in (select f2.col1, f2.col2 from foo_2 f2 where f2.id = data_1.id) loop
        ... do something else
    end loop;

  end loop;
end;
/

It will be a lot more efficient to do it like this:

begin
  for data_1 in (select f2.col1, f2.col2 from foo_2 f2
                 where f2.id in (select f1.id from foo_1 f1)) loop

     ... do something

  end loop;
end;
/

This can be processed without an excessive memory in JDBC using something like this:

String sql = "select f2.col1, f2.col2 from foo_2 f2 where f2.id in (select f1.id from foo_1 f1)";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
   String col1_value = rs.getString(1);
   int    col2_value = rs.getInt(2);
   ... do something
}

The above code will only hold one row in memory, even if you process billions of rows. To be precise: the JDBC driver will actually pre-fetch more than one row. The default is 10 and can be changed. But even then you don't have any excessive memory usage.