Using cursors and getting result in Oracle PL/SQL with Java/JDBC
Solution 1
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.
Solution 2
@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.
Solution 3
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;
}
}
Comments
-
Rajat Garg almost 2 years
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; ENDHow 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.