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

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

hnmj
Guest
hnmj

n,n,

uikuy
Guest
uikuy

yhkhjkhjk

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 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
Guest
Karthik Marupeddi

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

Thank you.

ajay kumar sharma
Guest
ajay kumar sharma

i am ajay kumar sharma