JDBC - CallableStatement Cursor and multiple out param(Oracle)

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

🔔 Subscribe to Ram N Java on YouTube

Why Use JDBC CallableStatement?

In enterprise database applications, business operations and complex SQL queries are frequently encapsulated inside stored procedures and functions residing in the database.

The CallableStatement interface in JDBC is specifically designed to execute database stored procedures. It supports IN parameters (values passed to the procedure), OUT parameters (values returned by the procedure), and even complex types like Oracle SYS_REFCURSOR that return full result sets back to Java.

The Oracle Stored Procedure (GET_COUNTRIES)

Here is the PL/SQL stored procedure that accepts a region ID as an input parameter and returns country data using an Oracle REF CURSOR:

CREATE OR REPLACE PROCEDURE GET_COUNTRIES (
    p_country_cursor OUT SYS_REFCURSOR,
    p_region_id_in   IN  NUMBER
)
AS
BEGIN
    OPEN p_country_cursor FOR
        SELECT country_id, country_name, region_id
        FROM countries
        WHERE region_id = p_region_id_in;
END GET_COUNTRIES;

Java Program: Invoking CallableStatement with Cursors

This complete Java example establishes an Oracle connection, prepares the PL/SQL call, registers the OUT cursor parameter using OracleTypes.CURSOR, and iterates over the returned 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 java.util.Scanner;
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();
        Scanner scanner = new Scanner(System.in);

        System.out.print("Enter Region ID (e.g., 1, 2, 3 or 0 to exit): ");
        int regionId = scanner.nextInt();

        if (regionId > 0) {
            demo.getCountryInformation(regionId);
        }
        scanner.close();
    }

    public void getCountryInformation(int regionId) {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // Prepare the stored procedure call
            String plsql = "{call GET_COUNTRIES(?, ?)}";
            cstmt = conn.prepareCall(plsql);

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

            // Set IN parameter (Region ID)
            cstmt.setInt(2, regionId);

            // Execute the stored procedure
            cstmt.executeQuery();

            // Retrieve the ResultSet from the 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 regId = rs.getInt("region_id");

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

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (cstmt != null) cstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Key Steps to Remember

• Use conn.prepareCall("{call PROCEDURE_NAME(?, ...)}") with curly brace escape syntax.
• Register OUT parameters using cstmt.registerOutParameter(index, sqlType) before executing.
• For Oracle REF CURSOR output, register as OracleTypes.CURSOR and cast cstmt.getObject(index) to ResultSet.
• Always close the ResultSet, CallableStatement, and Connection in the finally block.

Related Tutorials You Might Enjoy

• Oracle Database Connectivity with JDBC DataSource: Explained
• JDBC Database Connection: How to Connect Java to a Database
• JDBC Architecture Explained: How Java Connects to Any Database

---

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

Stored Procedure :

create or replace PROCEDURE GET_COUNTRIES
(
  COUNTRY_CURSOR OUT SYS_REFCURSOR,
  REGION_ID_IN IN NUMBER,
  COUNTRY_ID_OUT OUT VARCHAR2, 
  COUNTRY_NAME_OUT OUT VARCHAR2,
  REGION_ID_OUT OUT VARCHAR2  
) AS 
BEGIN
  OPEN COUNTRY_CURSOR FOR 
  SELECT COUNTRY_ID,COUNTRY_NAME,REGION_ID INTO COUNTRY_ID_OUT,COUNTRY_NAME_OUT,REGION_ID_OUT
  FROM COUNTRIES WHERE REGION_ID=REGION_ID_IN;
END GET_COUNTRIES;

Click the below Image to Enlarge

JDBC - CallableStatement Cursor and multiple out param(Oracle) 

JDBC - CallableStatement Cursor and multiple out param(Oracle) 

JDBC - CallableStatement Cursor and multiple out param(Oracle)