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 the Author

author image
mkyong
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

avatar
10 Comment threads
1 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
10 Comment authors
DannyJulianJulianManikantasam Recent comment authors
newest oldest most voted
Aashish K Shandilya
Guest
Aashish K Shandilya

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 #… Read more »

Danny
Guest
Danny

Hi, is in mySQL is the same sintax?

sam
Guest
sam

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
Guest
Vivek

Hi,

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

Julian
Guest
Julian

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
Guest
Manikanta

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

Oleg Aizatulin
Guest
Oleg Aizatulin

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

Shivkumar Vishweshwaraiah
Guest
Shivkumar Vishweshwaraiah

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
Guest
Umair Aslam

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

trackback
JDBC Tutorials

[…] JDBC CallableStatement – Stored Procedure CURSOR example Stored procedure CURSOR parameter via JDBC CallableStatement. […]

Julian
Guest
Julian

I see no reason why you can’t just execute it. Below is a test which returns a list for a dropdown selection into a web page retrieved from a MySQL database. And you can still catch you exceptions. It might look strange at the database connection part, but that is just because it connects through a Glassfish web server connection pool and not straight through JDBC. Also it must look like a lot of code, but with the right IDE, such as Netbeans, 90% of it is written for you. ##### JAVA MODEL ##### package com.bitts.model; import java.sql.Date; public class… Read more »