3

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:



      String SQL_SELECT = 
        "select * from act t1 where t1.G074=:G074 AND t1.G542=:G542 AND 
        nvl(t1.svhlick,' ')=nvl(:svhlick,' ') 
        and  t1.svhlic=:svhlic 
        and t1.sf$version =
          (select max(sf$version) from act t2 where t2.G074=t1.G074 and t2.G542=t1.G542 AND nvl
        (t2.svhlick,' ')=nvl(t1.svhlick,' ') and t2.svhlic=t1.svhlic)";
        Query q = em.createNativeQuery(SQL_SELECT, Act.class);
        ….
           q.setParameter("svhlick", entity.getSvhlick());
         …

the query uses Oracle's NVL function to handle null parameters. However, when I run this code, it fails with an error on the q.getResultList () call.

The error is as follows:

ORA-01465:  invalid hex number

Now, i'm resolved this problem as 

  q.setParameter("svhlick", (entity.getSvhlick() == null ? " " : entity.getSvhlick()));

But this'is solution is not good.

Database Oracle 11gR2 win1251 encoding.

Netbeans 7.3.1 project encoding UTF-8.

entity.getSvhlick() - String svhlick in Oracle - varchar2(1)

I think the problem may be due to different encoding.

Any Help?

K Guru
  • 1,292
  • 2
  • 17
  • 36
asu
  • 51
  • 1
  • 8

1 Answers1

0

I had the same issue and solved it by specifiying the type like:

q.setParameter("svhlick", entity.getSvhlick(), StandardBasicTypes.STRING);

see https://forum.hibernate.org/viewtopic.php?f=1&t=984725

apptaro
  • 279
  • 2
  • 8