Cursors: Retrieving ResultSet by executing DB Procedure or Functions

Usually we get one or more variable returned values from procedure or function.

But if we want to execute and SQL query as part of procedure/function and to get those results in JDBC program, then we need to use SYS_REFCURSOR type as return type.

Procedure:

create or replace procedure getEmps(emps OUT SYS_REFCURSOR, sal IN number)
as
BEGIN
open emps for
select employee_id from employees where salary < sal;
END getEmps;
/

Function:

create or replace function getEmployees(sal IN number) return SYS_REFCURSOR

as

Employees SYS_REFCURSOR;

BEGIN

open Employees for

select employee_id from employees where salaray < sal;

return Employees;

END getEmployees;

/

Program:

package com;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

public class CallableSt2 {

Connection con = null;

CallableSt2() {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "HR", "HR");

} catch(Exception e) {

e.printStackTrace();

}

}

public void testProcCursor() throws Exception {

CallableStatement cst = con.prepareCall("{call getEmps(?,?)}");

cst.setFloat(2,  4000.0f);

cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cst.execute();

ResultSet rs = (ResultSet) cst.getObject(1);

while(rs.next()) {

System.out.println(rs.getInt(1));

}

}

public void testFuncCursor() throws Exception {

CallableStatement cst = con.prepareCall("{?=call getEmployees(?)}");

cst.setFloat(2, 4000.0f);

cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cst.execute();

ResultSet rs = (ResultSet) cst.getObject(1);

while(rs.next()) {

System.out.println(rs.getInt(1));

}

}

static public void main(String[] args) throws Exception {

CallableSt2 test = new CallableSt2();

test.testProcCursor();

test.testFuncCursor();

}

}