How to execute an Oracle stored procedure from Java code

This is a working Java method that can be used to execute an Oracle stored procedure. It can be linked to standard Mbo methods, application beans, actions, toolbar buttons, etc.

public int callOracleProcedure(String procName, int arg1) throws MXException, RemoteException
{
  int ret;

  logger.info("Calling " + procName + "(" + arg1 + ")");

  MXServer mxServer = MXServer.getMXServer();
  UserInfo userInfo = mxServer.getSystemUserInfo();
  Connection dbConnection = mxServer.getDBManager().getConnection(userInfo.getConnectionKey());

  try
  {
    CallableStatement cs = dbConnection.prepareCall("{call " + procName + "(?)}");
    cs.setInt(1, arg1);
    ret = cs.executeUpdate();
  }
  catch (SQLException e)
  {
    logger.error("SQL Error", e);
    throw new MXApplicationException("Error in procedure " + procName, e.getMessage());
  }
  finally
  {
    mxServer.getDBManager().freeConnection(userInfo.getConnectionKey());
  }

  return ret;
}

Labels: , , , ,