Main Tutorials

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

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
16 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
ImPrakash
6 years ago

HI,
Can we call the stored procedure from Android
If Yes, How to get it done?

Oduntan Olalekan
6 years ago

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

Victor Moraes
7 years ago

What about OUT parameters?

Vinoth S
7 years ago

How to insert list of objects which has in/Out parameters in procedure

Manu
7 years ago

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

Luke schamberger
8 years ago

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.

becas
8 years ago

how to do i call combo-box value using callable statement please refer me in detail please

becas
8 years ago

can we

Chirag
9 years ago

Can we pass REF CURSOR as IN parameter, like we can say passing ARRAY to procedure

dev
11 years ago

This is great. Helped saving lots of time.

Amit
12 years ago

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

hnmj
4 years ago
Reply to  Amit

n,n,

uikuy
4 years ago
Reply to  hnmj

yhkhjkhjk

Padam Dhariwal
11 years ago
Reply to  Amit

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.

Karthik Marupeddi
10 years ago
Reply to  Padam Dhariwal

Hi MK can you please show us example with Spring Storeprocdure API.

Thank you.

ajay kumar sharma
8 years ago

i am ajay kumar sharma