JDBC Tutorial - JDBC Stored Procedure OUT Parameter

• « Previous

The following sections show how to call a stored procedure with both IN and OUT parameters.

A stored procedure for Oracle database written in PL/SQL language is listed as follows.

There are four parameters in the procedure and the last three of them are OUT parameters which means data will be passed out from those parameters.

CREATE OR REPLACE PROCEDURE getPERSONByUserId(
     p_userid IN PERSON.USER_ID%TYPE,
     o_username OUT PERSON.USERNAME%TYPE,
     o_createdby OUT  PERSON.CREATED_BY%TYPE,
     o_date OUT PERSON.CREATED_DATE%TYPE)
IS
BEGIN

  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  PERSON WHERE USER_ID = p_userid;

END;
/

Example

The Java code to call a stored procedure is listed as follows and the OUT parameters is used in getXXX() method from the CallableStatement.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
// w  w  w.ja v  a 2 s . c om
public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    CallableStatement callableStatement = null;
    String getPERSONByUserIdSql = "{call getPERSONByUserId(?,?,?,?)}";
    callableStatement = dbConnection.prepareCall(getPERSONByUserIdSql);

    callableStatement.setInt(1, 10);
    callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(4, java.sql.Types.DATE);

    callableStatement.executeUpdate();

    String userName = callableStatement.getString(2);
    String createdBy = callableStatement.getString(3);
    Date createdDate = callableStatement.getDate(4);

    System.out.println("UserName : " + userName);
    System.out.println("CreatedBy : " + createdBy);
    System.out.println("CreatedDate : " + createdDate);
    callableStatement.close();
    dbConnection.close();
  }
}

• « Previous