Search code examples
jakarta-eejdbcweblogicweblogic-10.xjava-6

Oracle Stored Procedure Broke after Migrating to Weblogic

I'm migrating my Java EE application from Java 1.4 to Java 6 and moving from OC4J to WebLogic 10.3.3. I'm running into a problem with a stored procedure call that worked in Java 1.4 on OC4J, but is no longer working in Java 6 on WebLogic. The stored procedure definition looks like:

create or replace procedure sp_report
    (
    /* snip 68 parameters */
    rs OUT pkg_recordset.rs_ref_cursor
     )
AS ...

Where rs_ref_cursor is:

type rs_ref_cursor is Ref CURSOR;

My java code looks like this:

public final static String CALL_REPORT = "{call SP_REPORT"
    + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
    + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";

...

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:@[server]:[port]:[sid]", "[username]", "[password]");
cstmt = conn.prepareCall(CALL_ALPHA_REPORT);
/* set 68 other parameters */
cstmt.registerOutParameter("rs", OracleTypes.CURSOR);

...

This always results in this exception on the call to registerOutParameter:

SQL Exception: 17004: Invalid column type: 246
java.sql.SQLException: Invalid column type: 246
  at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
  at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
  at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3532)
  at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:127)
  at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:2181)
  at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:2094)
  at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1593)
  ...

A few things I've tried that all result in the same error:

  1. Switching from ojdbc14.jar to ojdbc6.jar from Oracle's website
  2. Switching from ojdbc14.jar to ojdbc6.jar from weblogic's lib folder
  3. Using the JDBC connection defined in the WebLogic console (both Thin and Thin XA drivers)
  4. Using java.sql.Types.OTHER instead of OracleTypes.CURSOR (Although the number after the Oracle exception changes from 246 to 1111)
  5. Moving the registerOutParameter statement to before I set the other 68 parameters
  6. Various combinations of the above

Thanks for any help.

Solution

  • I found the solution.

    Short version: don't enable fast-swap in weblogic.xml

    Longer version: enabling fast swap screws up how ints work, so instead of the value of OracleTypes.CURSOR being -10, it was actually 246.

    I was having the exact same problem as this guy: https://forums.oracle.com/forums/thread.jspa?threadID=1104780