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

The "Code-Rich" Organization: How Automatic Code Generation Will Revolutionize Everything

  I put on paper a couple of thoughts about AI and Large Language Models (LLM) for automatic code generation in the development process. My thesis is that the current organizations are mostly “code-thin”, where only the basic Business Processes are modeled through software, while in the future we will have “code-rich" organizations boosted through the abundant and cheap AI and LLM Generated software code. These “code-rich” organizations, where every possible business process is software based - will outperform the usual "code-thin" organizations, where software is “hand-made” and expensive. Feedback would be very, very appreciated. https://www.linkedin.com/pulse/code-rich-organization-how-automatic-code-generation-de-collibus

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.