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

@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.

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()

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"