Set SP params by name - new feature added in oracle 10g jdbc driver

Everyone would try not to hard-code the signature of stored procedure (SP) in all java applications requiring database interaction through SPs. One of many ways could be having the signature defined as an xml config and application can intelligently manage any changes to xml config without any code changes. Most of the complexity in doing so would result from managing the sequence numbers of parameters.

From Oracle10g onwards this shall not be the case anymore as 10g jdbc drivers support SP invocation by param names along with sequence numbers. Unfortunately this is not the case with Sybase yet (have tested with jconn3.jar). Below is the sample code I have used for testing this feature with Oracle driver ver.10.2.0.1.0

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class SPTester
{
  private static CallableStatement setParamsByName(CallableStatement sp) throws Exception
  {
    sp.registerOutParameter("param1", Types.NUMERIC);
    sp.registerOutParameter("param2", Types.VARCHAR);
    sp.registerOutParameter("param3", Types.VARCHAR);
    sp.registerOutParameter("param4", Types.VARCHAR);
    sp.registerOutParameter("param5", Types.VARCHAR);
    sp.setString("param5", "12345");

    return sp;
  }
 
  private static CallableStatement setParamsBySeq(CallableStatement sp) throws Exception
  {
    sp.registerOutParameter(1, Types.NUMERIC);
    sp.registerOutParameter(2, Types.VARCHAR);
    sp.registerOutParameter(3, Types.VARCHAR);
    sp.registerOutParameter(5, Types.VARCHAR);
    sp.registerOutParameter(6, Types.VARCHAR);
    sp.setString(4, "12345");
   
    return sp;
  }

  private static void getParamsByName(CallableStatement sp) throws Exception
  {
    System.out.println("param1: " + sp.getString("param1"));
    System.out.println("param2: " + sp.getString("param2"));
    System.out.println("param3: " + sp.getString("param3"));
    System.out.println("param4: " + sp.getString("param4"));
    System.out.println("param5: " + sp.getString("param5"));
  }
 
  private static void getParamsBySeq(CallableStatement sp) throws Exception
  {
    System.out.println("param1: " + sp.getString(1));
    System.out.println("param2: " + sp.getString(2));
    System.out.println("param3: " + sp.getString(3));
    System.out.println("param4: " + sp.getString(5));
    System.out.println("param5: " + sp.getString(6));
  }

  public static void main(String[] args) throws Exception
  {
    Connection con = null;
    CallableStatement sp = null;
   
    try
    {
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
      con = DriverManager.getConnection("jdbc:oracle:thin:@<server>:<port>:<sid>", "usr", "pass");
      sp = con.prepareCall("{call <sp_name> (?, ?, ?, ?, ?, ?)}");

      int i = 10;
      if(i == 0) // by sequence
      {
        setParamsBySeq(sp);
        sp.execute();
        getParamsBySeq(sp);
      }
      else
      {
        setParamsByName(sp);
        sp.execute();
        getParamsByName(sp);
      }
    }
    catch(Exception e)
    {
      throw e;
    }
    finally
    {
      try
      {
        if(sp != null)
        {
          sp.close();       
        }
        if(con != null)
        {
          con.close();         
        }
      }
      catch(Exception e)
      {
        e.printStackTrace();
      }
    }
  }
}