Main Tutorials

JDBC CallableStatement – Stored Procedure CURSOR example

A JDBC CallableStatement example to call a stored procedure which returns a cursor.

Tested with Java 8 and Oracle database 19c

pom.xml

	<dependency>
		<groupId>com.oracle</groupId>
		<artifactId>ojdbc</artifactId>
		<version>8</version>
		<scope>system</scope>
		<systemPath>path.to/ojdbc8.jar</systemPath>
	</dependency>

1. JDBC CallableStatement

1.1 A PL/SQL stored procedure which returns a cursor.


	CREATE OR REPLACE PROCEDURE get_employee_by_name(
	   p_name IN EMPLOYEE.NAME%TYPE,
	   o_c_dbuser OUT SYS_REFCURSOR)
    AS
    BEGIN

      OPEN o_c_dbuser FOR
      SELECT * FROM EMPLOYEE WHERE NAME LIKE p_name || '%';

    END;

1.2 JDBC example to call above stored procedure.

StoreProcedureCursor.java

package com.mkyong.jdbc.callablestatement;

import com.mkyong.jdbc.model.Employee;
import oracle.jdbc.OracleTypes;

import java.math.BigDecimal;
import java.sql.*;

public class StoreProcedureCursor {

    public static void main(String[] args) {

        String createSP = "CREATE OR REPLACE PROCEDURE get_employee_by_name( "
                + " p_name IN EMPLOYEE.NAME%TYPE, "
                + " o_c_dbuser OUT SYS_REFCURSOR) "
                + " AS "
                + " BEGIN "
                + "     OPEN o_c_dbuser FOR "
                + "     SELECT * FROM EMPLOYEE WHERE NAME LIKE p_name || '%'; "
                + " END; ";

        String runSP = "{ call get_employee_by_name(?,?) }";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl", "system", "Password123");
             Statement statement = conn.createStatement();
             CallableStatement cs = conn.prepareCall(runSP);
        ) {

            //conn.setAutoCommit(false);

            // create function
            statement.execute(createSP);

            cs.setString(1, "mk");

            // alternative
            //cs.registerOutParameter(2, Types.REF_CURSOR);
            cs.registerOutParameter(2, OracleTypes.CURSOR);

            // run SP
            cs.execute();

            // get refcursor and convert it to ResultSet
            ResultSet resultSet = (ResultSet) cs.getObject(2);
            while (resultSet.next()) {

                long id = resultSet.getLong("ID");
                String name = resultSet.getString("NAME");
                BigDecimal salary = resultSet.getBigDecimal("SALARY");
                Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");

                Employee obj = new Employee();
                obj.setId(id);
                obj.setName(name);
                obj.setSalary(salary);
                // Timestamp -> LocalDateTime
                obj.setCreatedDate(createdDate.toLocalDateTime());

                System.out.println(obj);

            }

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Download Source Code

References

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
10 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
sam
10 years ago

Here all the examples are given considering oracle database only.
It will b really helpful if u can post some of them for mysql also.

thanks in advance……

Vivek
10 years ago

Hi,

How to flush the out parameter ?
If I call “callOracleStoredProcCURSORParameter” method twice in my code it duplicate the resultsetvalue.
Any solution for this ?

Danny
6 years ago

Hi, is in mySQL is the same sintax?

Julian
6 years ago

Hi there. Same question. Why do you use callableStatement.executeUpdate. But my other question is, why even use a CallableStatement? Why not just execute a “Call procedure()”, as one would use for a normal in Java code defined query? It works by just executing a “Call procedure()” just fine. Tested it. If you call the procedure from the command line or DB IDE, and do the same with the same query used in the procedure, and they return the exact same output, it works fine.

Manikanta
10 years ago

OracleTypes.CURSOR shows error oracle types cannot be resolved to type please provide solution

Aashish K Shandilya
10 years ago

Hi there !

I am using the same program to fetch data from Oracle DB in java with CURSOR.
The error that I am getting is –

A fatal error has been detected by the Java Runtime Environment:
#
# EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x61afe929, pid=7356, tid=7248
#
# JRE version: 6.0_21-b07
# Java VM: Java HotSpot(TM) Client VM (17.0-b17 mixed mode, sharing windows-x86 )
# Problematic frame:
# C [oracommon11.dll+0x14e929]
#
# An error report file with more information is saved as:
# F:\MyTeam02\workspace\Electronic_Voting_System\hs_err_pid7356.log
#
# If you would like to submit a bug report, please visit:
# http://java.sun.com/webapps/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#

Need Help !

Oleg Aizatulin
10 years ago

Why do you use callableStatement.executeUpdate instead of callableStatement.execute?

Shivkumar Vishweshwaraiah
10 years ago

Could you please provide code listing for a Spring JdbcTemplate Stored Procedure call (for Oracle)? Also note that the stored procedure has both input and output parameters, AND it returns a result set (not via OUT parameters).

Umair Aslam
11 years ago

Can you please provide the script for mysql stored procedure of above example ??