Calling Oracle stored procedure from Java

Question

I'm calling an Oracle stored function using JDBC thin driver. Following is the code.

class testSP 
{ 
  public static void main (String args []) 
                     throws SQLException, ClassNotFoundException 
  { 
      String driver_class = "oracle.jdbc.driver.OracleDriver"; 
      String connect_string = "jdbc:oracle:thin:@xxx.xx.xx.xx:1521:xxxx"; 

      Connection conn; 

      Class.forName(driver_class); 
      conn = DriverManager.getConnection(connect_string, "xxxx", "xxxx"); 

      // OracleCallableStatement ocs = 
      //    (OracleCallableStatement)conn.prepareCall(
      //        "{? = call acpks_stmt_gen.fn_stmt_gen(?,?,?,?,?,?)}");
      CallableStatement ocs = 
         conn.prepareCall(
            "{? = call acpks_stmt_gen.fn_stmt_gen(?,?,?,?,?,?)}");

      ocs.registerOutParameter(1, java.sql.Types.ARRAY);

      ocs.setString(2, "144000014");
      ocs.setString(3, "RET");
      ocs.setString(4, "N");
      ocs.setString(5, "3");
      ocs.setNull(6, java.sql.Types.DATE) ;
      ocs.setNull(7, java.sql.Types.DATE);



      ocs.executeUpdate(); 

     // java.sql.ResultSet rs2 = (java.sql.ResultSet) ocs.getResultSet();

  }

}

When I call this I get an exception as following Exception in thread "main" java.sql.SQLException: ORA-03115: unsupported network datatype or representation

I'm using the thin driver ojdbc6.jar provided by oracle. and I'm using this in my class path.

Thanks.

Answer 1

Try

  ocs.setString(6, "") ;
  ocs.setString(7, "");

instead of

  ocs.setNull(6, java.sql.Types.DATE);
  ocs.setNull(7, java.sql.Types.DATE);

making use of the fact that in Oracle, the empty string is the same as NULL.

In my current project, I've encountered similar problems with NULLs that could be solved that way. (Yes, that's fugly)