JDBC - CallableStatement Cursor(Oracle)

🚀 Master advanced JDBC, Oracle database connectivity, and stored procedures!

🔔 Subscribe to Ram N Java on YouTube

Why Use Oracle Cursors with JDBC CallableStatement?

When working with Oracle databases in enterprise Java applications, database logic is often packaged inside PL/SQL stored procedures. Instead of returning a single scalar value, stored procedures can return tabular data via an Oracle SYS_REFCURSOR.

Using the JDBC CallableStatement interface, Java applications can execute stored procedures that open a cursor and return complete result sets back to Java for processing.

The PL/SQL Stored Procedure (GET_ALL_COUNTRIES)

Here is the Oracle stored procedure definition that opens a reference cursor to fetch all rows from the COUNTRIES table:

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

Complete Java Code Example

The following Java program connects to an Oracle database, prepares the callable procedure call, registers the OUT cursor parameter with OracleTypes.CURSOR, and reads records into a standard ResultSet:

package com.ram.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;

public class JDBCCallableStatementDemo {

    static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    static final String USER = "hr";
    static final String PASS = "hr";

    public static void main(String[] args) {
        JDBCCallableStatementDemo demo = new JDBCCallableStatementDemo();
        demo.getAllCountryInformation();
    }

    public void getAllCountryInformation() {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try {
            // Step 1: Register Oracle JDBC Driver
            Class.forName(JDBC_DRIVER);

            // Step 2: Establish connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // Step 3: Prepare the procedure call
            String plsql = "{call GET_ALL_COUNTRIES(?)}";
            cstmt = conn.prepareCall(plsql);

            // Step 4: Register the OUT parameter as an Oracle cursor
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);

            // Step 5: Execute stored procedure
            cstmt.executeQuery();

            // Step 6: Extract ResultSet from the OUT cursor parameter
            rs = (ResultSet) cstmt.getObject(1);

            System.out.println("Country ID | Country Name | Region ID");
            System.out.println("----------------------------------------");
            while (rs.next()) {
                String countryId = rs.getString("country_id");
                String countryName = rs.getString("country_name");
                int regionId = rs.getInt("region_id");

                System.out.println(countryId + " | " + countryName + " | " + regionId);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // Step 7: Clean up resources
            try {
                if (rs != null) rs.close();
                if (cstmt != null) cstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Key Steps Explained

• conn.prepareCall("{call GET_ALL_COUNTRIES(?)}"): Uses JDBC procedure escape syntax to prepare the call with a placeholder for the OUT cursor parameter.
• cstmt.registerOutParameter(1, OracleTypes.CURSOR): Instructs JDBC that parameter 1 will return an Oracle reference cursor.
• (ResultSet) cstmt.getObject(1): Casts the returned cursor object directly to a ResultSet, allowing you to iterate through rows seamlessly.
• The finally block guarantees proper cleanup of the ResultSet, CallableStatement, and Connection.

Related Tutorials You Might Enjoy

• JDBC CallableStatement: Cursors and Multiple Out Parameters in Oracle DB Stored Procedures
• Oracle Database Connectivity with JDBC DataSource: Explained
• JDBC Architecture Explained: How Java Connects to Any Database

---

Click here to watch in Youtube :
https://www.youtube.com/watch?v=G4chXa8n6rA&list=UUhwKlOVR041tngjerWxVccw

Stored Procedure:

create or replace 
PROCEDURE GET_ALL_COUNTRIES 
(
   country_cursor OUT SYS_REFCURSOR
) AS 
BEGIN
  OPEN country_cursor FOR 
   SELECT * FROM COUNTRIES ;
END GET_ALL_COUNTRIES;

Click the below Image to Enlarge

JDBC - CallableStatement Cursor(Oracle)

JDBC - CallableStatement Cursor(Oracle)

JDBC - CallableStatement Cursor(Oracle)

JDBCCallableStatementDemo.java