JDBC CallableStatement – Stored Procedure IN parameter example
A JDBC CallableStatement
example to call a stored procedure which accepts IN parameters.
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 to insert a row.
CREATE OR REPLACE PROCEDURE insert_employee(
p_name IN EMPLOYEE.NAME%TYPE,
p_salary IN EMPLOYEE.SALARY%TYPE,
p_date IN EMPLOYEE.CREATED_DATE%TYPE)
AS
BEGIN
INSERT INTO EMPLOYEE ("NAME", "SALARY", "CREATED_DATE") VALUES (p_name, p_salary, p_date);
COMMIT;
END;
1.2 JDBC example to call above stored procedure.
StoreProcedureInParameter.java
package com.mkyong.jdbc.callablestatement;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
public class StoreProcedureInParameter {
public static void main(String[] args) {
String createSP = "CREATE OR REPLACE PROCEDURE insert_employee( "
+ " p_name IN EMPLOYEE.NAME%TYPE, "
+ " p_salary IN EMPLOYEE.SALARY%TYPE, "
+ " p_date IN EMPLOYEE.CREATED_DATE%TYPE) "
+ " AS "
+ " BEGIN "
+ " INSERT INTO EMPLOYEE (\"NAME\", \"SALARY\", \"CREATED_DATE\") VALUES (p_name, p_salary, p_date); "
+ " COMMIT; "
+ " END; ";
String runSP = "{ call insert_employee(?,?,?) }";
try (Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "system", "Password123");
Statement statement = conn.createStatement();
CallableStatement callableStatement = conn.prepareCall(runSP)) {
// create or replace stored procedure
statement.execute(createSP);
//----------------------------------
callableStatement.setString(1, "mkyong");
callableStatement.setBigDecimal(2, new BigDecimal("99.99"));
callableStatement.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
// Run insertEmployee() SP
callableStatement.executeUpdate();
} 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
HI,
Can we call the stored procedure from Android
If Yes, How to get it done?
I really appreciate your idea given here. It really helped me to solved an issues having been tackling with for 3 days due to (?,?,?) whereas I used ? for only input 3 parameter [callableStatement.setString(1, sol);
callableStatement.setString(2, fromDate);
callableStatement.setString(3, toDate);] without considering callableStatement.registerOutParameter.
check the sample of the code below:
…….
try(CallableStatement callableStatement = con.prepareCall(“{call custom.newly_opened_account(?,?,?,?,?,?,?,?,?,?,?,?)}”)) {
callableStatement.setString(1, sol);
callableStatement.setString(2, fromDate);
callableStatement.setString(3, toDate);
callableStatement.registerOutParameter(4,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(5,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(6,java.sql.Types.DATE);
callableStatement.registerOutParameter(7,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(8,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(9,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(10,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(11,java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(12,java.sql.Types.VARCHAR);
……blah….blah
What about OUT parameters?
How to insert list of objects which has in/Out parameters in procedure
Is it possible to use java8 DateTime API with spring-jdbc stored procedure? For example, can I pass object of java8 LocalDateTime as input parameter, instead of java.util.Date
How can I find the reference to my own jdbc package, This is a great help, but I am very new to the field, and I am having to learn as I go. Thanks ahead of time.
how to do i call combo-box value using callable statement please refer me in detail please
can we
Can we pass REF CURSOR as IN parameter, like we can say passing ARRAY to procedure
This is great. Helped saving lots of time.
Nice article. One question – Can a stored procedure be executed using java.sql.Statement object instead of a CallableStatement? The stored procedure does not take any parameters
n,n,
yhkhjkhjk
Hi Sir,
I am struck at my project on the below details. I have learnt Struts 2.0 from reading your articles. I need you help in this if you can provide I will be thankful to you.
we are using Store Procedure for in order to insert and update.
I have seen your example in this Article, but struck on inserting store procedure having Table of Objects.
Please see the below Store Procedure and my comments in Blue and Bold I need to call this GBDF_SAVE_VAR_EQRAGG store procedure in order to insert data.
Please give your suggestion how to insert data using Callable Statement which has Table of Objects.
Below Store Procedure.
———————————-
create or replace
FUNCTION “GBDF_SAVE_VAR_EQRAGG”
(TipoAzione IN VARCHAR2
,Applicazione IN VARCHAR2
,DataRifChar IN VARCHAR2
,IDSito IN VARCHAR2
,IDAmbito IN VARCHAR2
,IDVar IN VARCHAR2 — identificativo C_VAR
,DescVar IN VARCHAR2
,SeqVar IN NUMBER
,TabellaVar IN VarCalcTab
,NumeroVar IN NUMBER
,DeltaForz IN NUMBER
,ValoreEqz IN NUMBER
,IDUtente IN VARCHAR2 DEFAULT NULL) RETURN NUMBER
create or replace
TYPE VARCALCTAB AS TABLE OF VarCalcRec ;
create or replace
TYPE VARCALCREC AS OBJECT
( VarSign CHAR(1)
, VarName VARCHAR2(20)) ;
Please give me suggestions on this .
Thanks,
Padam.
Hi MK can you please show us example with Spring Storeprocdure API.
Thank you.
i am ajay kumar sharma