JAVA EE: Oracle : Stored Procedure Cursor

Monday, 13 July 2015

Oracle : Stored Procedure Cursor

Master Java & Oracle Database!

Subscribe to Ram N Java for hands-on JDBC tutorials, practical database scripts, and clear step-by-step coding guides.

Overview: Returning Result Sets from Oracle Stored Procedures

In Oracle Database, stored procedures do not automatically return result sets like standard SQL queries or MySQL procedures. Instead, Oracle uses SYS_REFCURSOR (cursor reference) to stream multiple records back to the calling client application.

Using JDBC and the CallableStatement interface, Java applications can easily register an OUT parameter of type cursor, execute the procedure, and read the rows into a standard Java ResultSet.

Step 1: The Oracle Stored Procedure

To return all rows from a COUNTRY table, define an Oracle stored procedure that accepts a cursor as an OUT parameter:

CREATE OR REPLACE PROCEDURE get_all_countries(
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT country_id, country_name, region_id FROM countries;
END;
/

Step 2: Invoking the Procedure via JDBC

In Java, register the cursor output parameter using Oracle's cursor data type definition (OracleTypes.CURSOR), execute the procedure, and cast the returned object into a standard ResultSet:

String sql = "{call get_all_countries(?)}";
CallableStatement cstmt = connection.prepareCall(sql);

// Register cursor OUT parameter
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

// Execute procedure
cstmt.execute();

// Retrieve records into ResultSet
ResultSet rs = (ResultSet) cstmt.getObject(1);
while (rs.next()) {
    System.out.println("ID: " + rs.getString("country_id") + 
                       ", Name: " + rs.getString("country_name"));
}

Why Use Ref Cursors in Oracle?

Encapsulated Query Logic: SQL query definitions stay managed inside the database catalog rather than hard-coded into Java application layers.
Optimal Resource Consumption: Cursors stream records efficiently without buffering large datasets entirely in database memory.
Seamless Integration: Fits naturally into standard JDBC ResultSet iterations for developer productivity.

Recommended Oracle & CallableStatement Tutorials

Continue mastering Oracle database connectivity with these related tutorials from our channel: