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 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
13 Comment threads
4 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
15 Comment authors
uikuyhnmjImPrakashOduntan OlalekanVictor Moraes Recent comment authors
newest oldest most voted
Chirag
Guest
Chirag

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

ImPrakash
Guest
ImPrakash

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

Oduntan Olalekan
Guest
Oduntan Olalekan

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
Guest
Victor Moraes

What about OUT parameters?

Vinoth S
Guest
Vinoth S

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

Manu
Guest
Manu

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
Guest
Luke schamberger

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

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

becas
Guest
becas

can we

dev
Guest
dev

This is great. Helped saving lots of time.

trackback
Llamar Stores Procedures desde Netbeans

[…] […]

Amit
Guest
Amit

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

Padam Dhariwal
Guest
Padam Dhariwal

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

Karthik Marupeddi
Guest
Karthik Marupeddi

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

Thank you.

hnmj
Guest
hnmj

n,n,

uikuy
Guest
uikuy

yhkhjkhjk

ajay kumar sharma
Guest
ajay kumar sharma

i am ajay kumar sharma