Blog / 2010 / September / 24

Calling PL/SQL from Java

September 24, 2010

I had a need to call a PL/SQL procedure from Java, where it took one input parameter and returned six output parameters.

This is done by creating a CallableStatement instead of the normal PreparedStatement, and then you need to register each output parameter before calling execute().

String lExecuteSql = "{call PL_SQL_PROCEDURE(?,?,?,?,?,?,?)}";
CallableStatement lStatement = null;

try
{
  Connection lConnection = this.getConnection();
  CallableStatement lStatement = lConnection.prepareCall(pQuery);
  lStatement.setString(1, pSerial);
  lStatement.registerOutParameter(2, Types.VARCHAR);
  lStatement.registerOutParameter(3, Types.VARCHAR);
  lStatement.registerOutParameter(4, Types.INTEGER);
  lStatement.registerOutParameter(5, Types.VARCHAR);
  lStatement.registerOutParameter(6, Types.VARCHAR);
  lStatement.registerOutParameter(7, Types.VARCHAR);
  lStatement.execute();

  lStatusCode = lStatement.getString(2);
  lStatus = lStatement.getString(3);
  lBranchSerial = lStatement.getInt(4);
  lBranchName = lStatement.getString(5);
  lBranchTelephone = lStatement.getString(6);
  lAgreementNumber = lStatement.getString(7);

}
catch (SQLException lSqlEx)
{
  throw new RuntimeException(lSqlEx);
}
finally
{
  this.clearupStatement(lStatement);
}

Tags: plsql java output parameter