Skip to main content

Hibernate Stored Procedure with first parameter not "OUT"


I spent some days trying to call a Stored Procedure through Hibernate. I used CreateSQLQuery  and I made my own custom transformer, but I could not get it work. I keep geeting Wrong Type and Parameter of procedure exception.

My procedure was like the following

    procedure GET_BY_EMPLOYEE (
        P_EMPLOYEE_ID   in       EMPLOYEE_ID%TYPE
      , P_DATE_FROM  in       date
      , P_DATE_TO    in       date
      , P_REF           out      CURSORREFERENCETYPE);

After days of searching, I found this

http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch13.html#sp_query

For Oracle the following rules apply:

  • A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define aREF CURSOR type. See Oracle literature for further information.

So there is no way to get this procedure to work in hibernate. I will use a plain JDBCTemplate. 

Comments

Popular posts from this blog

Building bitcoin/litecoin on mac os x missing EC.h

For my own fun, I was playing around with bitcoin, to check how the parameters of the cryptocurrencies can be modified. After recent upgrades to my OS, I could not build any longer the bitcoind. Seems like something changed on that side. Now this command ./configure --with-gui=qt5 --enable-debug Was sistematically producing  configure: error: OpenSSL ec header missing EC is the file for elliptic curve cryptography. I had brew correctly configured, and the header files were all correctly present. After some time spent inspecting the issue, on github I was able to find the solution for this problem. I simply add to export the following export LDFLAGS=-L/usr/local/opt/openssl/lib export CPPFLAGS=-I/usr/local/opt/openssl/include There is aksi pull request on bitcoin for that, I hope I could save you some time https://github.com/bitcoin/bitcoin/pull/6885/files?diff=split&unchanged=expanded

Multiple controllers with Spring Boot

Remember, when you want to have multiple controllers with Spring Boot, you should always name them differently in the annotation, otherwise they will not work So these two together will NOT work (or just one of them will work) These two instead WILL work.