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
$ git clone https://github.com/mkyong/java-jdbc.git
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……
Hi,
How to flush the out parameter ?
If I call “callOracleStoredProcCURSORParameter” method twice in my code it duplicate the resultsetvalue.
Any solution for this ?
Hi, is in mySQL is the same sintax?
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.
OracleTypes.CURSOR shows error oracle types cannot be resolved to type please provide solution
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 !
Why do you use callableStatement.executeUpdate instead of callableStatement.execute?
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).
Can you please provide the script for mysql stored procedure of above example ??