Call Oracle Stored Procedure from Java

Using IN and OUT Parameters

 

Oracle Stored Procedure

CREATE OR REPLACE PROCEDURE sp_DemoInOut
(
  inString    IN          VARCHAR2,
  outString OUT       VARCHAR2,
  inOutInt    IN OUT  NUMBER
)
 IS
 BEGIN

  outString  :=  inString;
  inOutInt    := 100;
  DBMS_OUTPUT.PUT_LINE('In my proc;');

 END;

 

Testing Stored Procedure from Oracle

DECLARE

  outstr VARCHAR2(20);

  outint NUMBER(8,0);

 

BEGIN

 

  outint := 10;

  sp_DemoInOut('abcd',outstr, outint);

  DBMS_OUTPUT.PUT_LINE('outstr: '|| outstr || ' outint: ' || outint);

 

END;

 

Java Demo to Call Oracle Stored Procedure

package db;

 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import oracle.jdbc.driver.*;

 

 

public class StoredProcInOut {

  public static void main(String[] args) throws Exception {

    Connection conn = getOracleConnection();

    // Step-2: identify the stored procedure

    String proc3StoredProcedure = "{ call sp_DemoInOut(?, ?, ?) }";

    // Step-3: prepare the callable statement

    CallableStatement cs = conn.prepareCall(proc3StoredProcedure);

    // Step-4: set input parameters ...

    // first input argument

    cs.setString(1, "abcd");

    // third input argument

    cs.setInt(3, 10);

    // Step-5: register output parameters ...

    cs.registerOutParameter(2, OracleTypes.VARCHAR);

    cs.registerOutParameter(3, OracleTypes.NUMBER);

    // Step-6: execute the stored procedures: proc3

    cs.execute();

    // Step-7: extract the output parameters

    // get parameter 2 as output

    String param2 = cs.getString(2);

    // get parameter 3 as output

    int param3 = cs.getInt(3);

    System.out.println("param2=" + param2);

    System.out.println("param3=" + param3);

    conn.close();

  }

 

  public static Connection getOracleConnection() throws Exception {

    String driver = "oracle.jdbc.driver.OracleDriver";

    String url = "jdbc:oracle:thin:@localhost:1521:xe";

    String username = "";

    String password = "";

 

    Class.forName(driver); // load Oracle driver

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;

  }

}