22 Jan 2011

ADF BC. PL/SQL procedure params.

Introduction
Some days ago I had a task to get names and types of parameters of some PL/SQL procedure from BC model. I asked Google to help me and was surprised. Almost nothing how to do it. 
Information about PL/SQL procedure's parameters can be very useful to build for example a wrapper to call PL/SQL procedures instead of writing ugly code using JDBC API directly. In this post I will try to put some light in this dark corner.

 We can get description of procudure's parameters using JDBC API DatabaseMetadata interface and it's method getProcedureColumns. The following static method getSoredProcParams prints some information about PL/SQL procedure's parameters:

public static ResultSet getStoredProcParams(DBTransaction dbtransaction,
                                            String packageName,
                                            String procedureName) {
  if (procedureName == null || procedureName.isEmpty())
      return null;
  Connection conn = null;
  ResultSet rs = null;

  //We need this PreparedStatement to get Connection only
  PreparedStatement statement =
      dbtransaction.createPreparedStatement("commit", 1);

  try {

      conn = statement.getConnection();
      String upperPackageName =
          (packageName == null ? null : packageName.toUpperCase());
      rs =
conn.getMetaData().getProcedureColumns(upperPackageName, null, 
                                  procedureName.toUpperCase(),
                                  null);

      while (rs.next()) {
          System.out.println("ParamName = " +
                             rs.getString("COLUMN_NAME"));
          System.out.println("ParamType = " + 
                             rs.getString("DATA_TYPE"));
          System.out.println("ParamTypeName = " +
                             rs.getString("TYPE_NAME"));
          System.out.println(" ");
      }
  } catch (SQLException sqlerr) {
      throw new JboException(sqlerr);
  } finally {
      try {
          if (statement != null) {
              statement.close();
          }
      } catch (SQLException closeerr) {
          throw new JboException(closeerr);
      }
  }

  return rs;
}

No comments:

Post a comment

Post Comment