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

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. Read all published posts by

Comments

avatar
3000
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
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#

Need Help !

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 Vug { private int vugdUid; private int vugdVugUid; private String vugdDescription; private String vugdReferenceCode; private Date vugdStartDate; private Date vugdEndDate; private Date vugdCreatedOn; private String vugdCreatedBy; private Date vugdModifiedOn; private String vugdModifiedBy; private String vugdName; /** * @return the vugdUid */ public int getVugdUid() { return vugdUid; } /** * @param vugdUid the vugdUid to set */ public void setVugdUid(int vugdUid) { this.vugdUid = vugdUid; } /** * @return the vugdVugUid */ public int getVugdVugUid() { return vugdVugUid; } /** * @param vugdVugUid the vugdVugUid to set */ public void setVugdVugUid(int vugdVugUid) { this.vugdVugUid = vugdVugUid; } /** * @return the vugdDescription */ public String getVugdDescription() { return vugdDescription; } /** * @param vugdDescription the vugdDescription to set */ public void setVugdDescription(String vugdDescription) { this.vugdDescription = vugdDescription; } /** * @return the vugdReferenceCode */ public String getVugdReferenceCode() { return vugdReferenceCode; } /** * @param vugdReferenceCode the vugdReferenceCode to set */ public void setVugdReferenceCode(String vugdReferenceCode) { this.vugdReferenceCode = vugdReferenceCode; } /** * @return the vugdStartDate */ public Date getVugdStartDate() { return vugdStartDate; } /** * @param vugdStartDate the vugdStartDate to set */ public void setVugdStartDate(Date vugdStartDate) { this.vugdStartDate = vugdStartDate; } /** * @return the vugdEndDate */ public Date getVugdEndDate() { return vugdEndDate; } /** * @param vugdEndDate the vugdEndDate to set */ public void setVugdEndDate(Date vugdEndDate) { this.vugdEndDate = vugdEndDate; } /** * @return the vugdCreatedOn */ public Date getVugdCreatedOn() { return vugdCreatedOn; } /** * @param vugdCreatedOn the vugdCreatedOn to set */ public void setVugdCreatedOn(Date vugdCreatedOn) { this.vugdCreatedOn = vugdCreatedOn; } /** * @return the vugdCreatedBy */ public String getVugdCreatedBy() { return vugdCreatedBy; } /** * @param vugdCreatedBy the vugdCreatedBy to set */ public void setVugdCreatedBy(String vugdCreatedBy) { this.vugdCreatedBy = vugdCreatedBy; } /** * @return the vugdModifiedOn */ public Date getVugdModifiedOn() { return vugdModifiedOn; } /** * @param vugdModifiedOn the vugdModifiedOn to set */ public void setVugdModifiedOn(Date vugdModifiedOn) { this.vugdModifiedOn = vugdModifiedOn; } /** * @return the vugdModifiedBy */ public String getVugdModifiedBy() { return vugdModifiedBy; } /** * @param vugdModifiedBy the vugdModifiedBy to set */ public void setVugdModifiedBy(String vugdModifiedBy) { this.vugdModifiedBy = vugdModifiedBy; } /** * @return the vugdName */ public String getVugdName() { return vugdName; } /** * @param vugdName the vugdName to set */ public void setVugdName(String vugdName) { this.vugdName = vugdName; } @Override public String toString() { return “, Vug [vugdName=” + vugdName + “, vugdUid=” + vugdUid + “, vugdVugUid=” + vugdVugUid + “, vugdName=” + vugdDescription + “, vugdDescription=” + vugdDescription + “, vugdReferenceCode=” + vugdReferenceCode + “, vugdStartDate=” + vugdStartDate + “, vugdEndDate=” + vugdEndDate + “, vugdCreatedOn=” + vugdCreatedOn + “, vugdCreatedBy=” + vugdCreatedBy + “, vugdModifiedOn=” + vugdModifiedOn + “, vugdModifiedBy=” + vugdModifiedBy + “]”; } } ##### JAVA DAO ##### package com.bitts.dao; import java.sql.Connection; import com.bitts.model.Vug; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; import javax.naming.InitialContext; import javax.sql.DataSource; public class VugDao { private Connection connection; public List getAllVugs() throws Exception { InitialContext ctx = new InitialContext(); DataSource ds = (javax.sql.DataSource)ctx.lookup(“jdbc/bitts”); connection = ds.getConnection(); Statement statement = connection.createStatement(); List vugs = new ArrayList(); try { ResultSet rs = statement.executeQuery(“CALL bitts.GetAllVUGs();”); while (rs.next()) { Vug vug = new Vug(); vug.setVugdName(rs.getString(“vugd_name”)); vug.setVugdUid(rs.getInt(“vugd_uid”)); vug.setVugdVugUid(rs.getInt(“vugd_vug_uid”)); vug.setVugdDescription(rs.getString(“vugd_description”)); vug.setVugdReferenceCode(rs.getString(“vugd_reference_code”)); vug.setVugdStartDate(rs.getDate(“vugd_start_date”)); vug.setVugdEndDate(rs.getDate(“vugd_end_date”)); vug.setVugdCreatedOn(rs.getDate(“vugd_created_on”)); vug.setVugdCreatedBy(rs.getString(“vugd_created_by”)); vug.setVugdModifiedOn(rs.getDate(“vugd_modified_on”)); vug.setVugdModifiedBy(rs.getString(“vugd_modified_by”)); vugs.add(vug); } connection.close(); connection = null; statement.close(); statement = null; } catch (SQLException e) { Logger.getLogger(VugDao.class.getName()).log( Level.SEVERE, null, e); } finally { /* * close any jdbc instances here that weren’t * explicitly closed during normal code path, so * that we don’t ‘leak’ resources… */ if (statement != null) { try { statement.close(); } catch (SQLException sqlex) { // ignore, as we can’t do anything about it here } statement = null; } if (connection != null) { try { connection.close(); } catch (SQLException sqlex) { // ignore, as we can’t do anything about it here } connection = null; } } return vugs; } } ##### PROCEDURE ##### DELIMITER $$ CREATE DEFINER=bitts@% PROCEDURE GetAllVUGs() BEGIN SELECT vug_name AS vugd_name, vugd_detail.* FROM bitts.vug_virtual_user_group, bitts.vugd_detail WHERE vugd_vug_uid = vug_uid; END$$ DELIMITER ;