Oracle Error When Setting NULL Parameter Value

o_swas · **Joined:** Thu Mar 23, 2006 4:02 pm **Posts:** 4

Hello,

I'm receiving an Oracle error when attempting to execute a native SQL query that uses a null parameter value. I've boiled it down to this test case:

Code:

final String SQL = "select username as userName from all_users where username = NVL(?, username)";
SQLQuery sqlQuery = getSession().createSQLQuery(SQL);
sqlQuery.setParameter(0, null);

List results = sqlQuery.list();

As you can see, the query uses Oracle's NVL function to handle null parameters.

However, when I run this code, it fails with an error on the sqlQuery.list() call. The error is as follows:

Code:

org.hibernate.exception.GenericJDBCException: could not execute query
...
...
Caused by: java.sql.SQLException: ORA-01465: invalid hex number

For some reason it doesn't like a null parameter, which should be valid and legal. If I use a string value for the parameter it works fine, but for some reason it doesn't like a null value.

I'm using Hibernate 3.2.6.ga and Oracle 10, along with ojdbc14.jar as the Oracle driver.

Any hints or suggestions? This is pretty critical for me as I'm trying to use it in a reporting framework.

Thank you!!!

-Ryan

Katy · **Posted:** Wed Mar 12, 2008 3:33 pm

I have the same config (hibernate 3.2.6 GA, Oracle 10g and ojdbc14.jar) and your code works with no problem. Maybe you're looking for the problem in the wrong place.

Could you please post the definition of the all_users table so I can perform a real test?

llefevre · **Posted:** Wed Mar 12, 2008 3:55 pm

And what about:

Code:

sqlQuery.setParameter(0, null, org.hibernate.Hibernate.STRING);

o_swas · **Joined:** Thu Mar 23, 2006 4:02 pm **Posts:** 4

llefevre wrote:

And what about:

Code:

sqlQuery.setParameter(0, null, org.hibernate.Hibernate.STRING);

Yes, this works, as I eventually found out on my own. However, having to specify the Hibernate type could have been a real problem, because I wanted to use code similar to this in a reporting framework where the SQL isn't known, and thus the data type of the potential null value isn't known.

Fortunately, though, I have some back door ways of figuring out what the Hibernate type is, so I can specify the Hibernate type anyways.