Missing IN or OUT parameter problem

[Mikhail]

I have really weird behaviour in only one call to stored procedure.
Sometimes this problem is stable. Sometimes it starts working after passing
the method once in a debuger. I can change sql to call different stored
procedure and it doesn't affect the behavioir. This is WLS813 and Oracle 9i.
I would appreciate any advice.

The source code is simple and is used in multiple places in the application
(I tried its different variations with the same result):

DataSource ds = EJBContext.getInstance().getDefaultDataSource();
conn = ds.getConnection();
stmt = conn.prepareCall("{ ? = development_pkg.Get_AddUnit_BlockList ( ?,
?, ? ) }");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setObject(2, developmentId);
stmt.setObject(3, isCallerInAllSocietyViewRole() ? null :
getCallerUserEntity().getSocietyId());
stmt.setString(4, getCallerName());
stmt.execute(); - this line generates an exception

Exception if isCallerInAllSocietyViewRole() is true (1st call after
restarting the server):
java.sql.SQLException: Missing IN or OUT parameter at index:: 2 at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at
oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1678)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2883)
at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
at
oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
at
weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:70)
at

Exception if isCallerInAllSocietyViewRole() is true (any consecuent call):
java.sql.SQLException: Missing IN or OUT parameter at index:: 1 at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at
oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1539)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2883)
at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
at
oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
at
weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:70)
at

Exception if isCallerInAllSocietyViewRole() is false:
java.lang.NullPointerException at
oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:728) at
oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:787)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
at
oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
at
weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:70)
at ...

Thanks,
Mikhail Stolpner

[Joe Weinstein]

Hi. So are you saying it *always* fails with a setObject(3, null), or only
after first running it the other way? This sounds like a problem with the
oracle drivers setObject() with null as input. What I would like to see is
if this works:

if ( isCallerInAllSocietyViewRole() )
stmt.setNull(3, Types.VARCHAR );
else
stmt.setObject( 3, getCallerUserEntity().getSocietyId() );

Joe

[Mikhail]

Hi Joe,

Thank you for your reply. I tried multiple different ways: setNull,
setObject. The behavior is the same. The following is the original code
(before I played with it) and it worked fine in WLS812:

DataSource ds = EJBContext.getInstance().getDefaultDataSource();
conn = ds.getConnection();

stmt = conn.prepareCall("{ ? = call development_pkg.Get_AddUnit_BlockList

( ?, ?, ? ) }");
stmt.registerOutParameter(1, OracleTypes.CURSOR);

stmt.setInt(2, developmentId.intValue());
if (isCallerInAllSocietyViewRole()) {
stmt.setNull(3, OracleTypes.INTEGER);
} else {
stmt.setInt(3, getCallerUserEntity().getSocietyId().intValue());
}
stmt.setString(4, getCallerName());
stmt.execute();

I created a BEA case but I think that it would be very difficult to
reproduce the problem as the same code works fine for me in all other
cases!!! And despite spending a lot of time I couldn't figure out why it is
failing in this particular case. This is not related to the stored procedure
as I changed the sql and it didn't change anything.

Sincerely,

Mikhail Stolpner

"Joe Weinstein" <joeN...@bea.com> wrote in message
news:4133A7B3...@bea.com...

[Mikhail]

Joe helped me to resolve the problem and just in case anyone else has
similar problem I am posting the resolution. I moved one line of code that
calls Entity Bean and that has fixed the problem. The following code works
fine but if you move line 3 after prepareCall it breaks.

1. DataSource ds = EJBContext.getInstance().getDefaultDataSource();
2. conn = ds.getConnection();
3. Integer societyId = isCallerInAllSocietyViewRole() ? null :
getCallerUserEntity().getSocietyId();
4. stmt = conn.prepareCall("{ ? = call development_pkg.Get_AddUnit_BlockList

( ?, ?, ? ) }");

5. stmt.registerOutParameter(1, OracleTypes.CURSOR);
6. stmt.setInt(2, developmentId.intValue());
7. stmt.setObject(3, societyId);
8. stmt.setString(4, getCallerName());
9. stmt.execute();

Thanks,
Mikhail

"Mikhail" <msto...@canada.com> wrote in message news:413384db$1@mail...