Hi,

I am using hibernate 3.2 with Spring and am facing a problem with passing Date parameters as null to an Oracle Stored procedure. The call to the stored procedure works fine if the date parameters are passed. But if it is not passed, it gives an exception PLS-00306: wrong number or types of arguments in call to PROC_GET_USERS

But the same procedure accepts nulls and returns values if I execute the procedure from command line from Sql Plus.

Any help on the same is appreciated.

-Rajesh

Use

<property name="show_sql">true</property>

and see what SQL Hibernate is sending to Oracle

On further debugging I found that there is a difference between the null binding that hibernate does and the result when we use a direct callable statement and bind null values along with proper parameter types. It seems that since hibernate does not know the parameter type before hand, if the value being bound is null, then it treats the value as SerializableType which is a subclass of NullableType. In this case the nullSafeSet method handles null parameters as follows:
Here the sqlType() function returns -3 which corresponds to Types.BINARY in java.sql.Types. I debugged the CallableStatement there and found that the binding it creates within is of type rawNullBinder. And this causes an SQL exception like java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to PROC_NAME. I think if the null binding was done with Types.NULL instead of Types.BINARY, this exception could be avoided. But the strange thing is that it happens only for Dates and Numeric parameters, there is no problem if the null parameter is of type string.

Are there any other solutions for this issue, or am I dwelving too much in to hibernate code?

Note: I am using the Spring hibernate template method:


Any suggestions are welcome.

Well I found the solution myself. Use the Query interface directly and in the setParameter pass the hibernate type of the prameter also. It works perfectly.