Executing Oracle Stored Procedure with Hibernate

Question

I have been trying to execute an Oracle Stored Procedure using Hibernate. This is not for production - but for a Java source parsing project, that I'm pursuing. To put it in simple terms, I'm not able to return a value from an Oracle Stored Proc.

I have searched and read all relevant links from SO, Hibernate community/documentation(Native SQL chapter) links and tried out the suggestions, but somehow couldn't get them to work. Below are my sources - I'm including only the relevant parts.

My Entity Class. I have reserved the first parameter for a PL/SQL OUT parameter.

Login.java

@NamedNativeQuery(
        name = "getLoginDet", 
        query = "call GET_LOGIN_DET(?,:userId)", 
        resultClass = Login.class)
@Entity
@Table(name = "T_LOGIN_DET")
public class Login {

Oracle Stored Proc : GET_LOGIN_DET.sql. The first parameter is OUT REFCURSOR as per Hibernate Spec

create or replace PROCEDURE GET_LOGIN_DET(listLogin OUT SYS_REFCURSOR,userId IN VARCHAR2)
AS 
BEGIN
OPEN listLogin FOR 
        SELECT *
        FROM T_LOGIN_DET 
        WHERE USER_ID = userId;
  END GET_LOGIN_DET;

My DAO Class : I'm binding only the named parameter, ignoring the first ? in the named Query.

Session session = sessionFactory.openSession();
List results = session.getNamedQuery("getLoginDet").setParameter("userId", u.getUserId()).list();

My Hibernate Config

<bean id="mysessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>
    <property name="configurationClass">
        <value>org.hibernate.cfg.AnnotationConfiguration</value>
    </property>
    <property name="annotatedClasses">
        <list>
            <value>com.cogn.gto.sea.employee.entity.Employee</value>
            <value>com.cogn.gto.sea.employee.entity.Department</value>
            <value>com.cogn.gto.sea.login.entity.User</value>
            <value>com.cogn.gto.sea.login.entity.Login</value>  
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
            <!-- <prop key="hibernate.hbm2ddl.auto">update</prop> -->
            <prop key="hibernate.show_sql">true</prop>
        </props>
    </property>
</bean>

However, I always end up with Hibernate QueryException / Java SQLException

Expected positional parameter count: 1, actual parameters: [] [{call GET_LOGIN_DET(?,:userId)}]

I have tried variations of {? = call GET_LOGIN_DET(:userId)} , call GET_LOGIN_DET(?,:userId) to no avail. My requirement is to call the procedure that I have listed and get the result back in the DAO class. Can someone lead me to what exactly I'm missing here ?

Saulius Next · Accepted Answer · 2016-12-15 15:35:34Z

1

I believe you have incorrectly declared call to stored procedure (curly brackets missing), try this:

@NamedNativeQuery(
        name = "getLoginDet", 
        query = "{call GET_LOGIN_DET(?,:userId)}", 
        resultClass = Login.class
        hints = {@QueryHint(name = "org.hibernate.callable", value = "true")})
@Entity
@Table(name = "T_LOGIN_DET")
public class Login {

answered Dec 15, 2016 at 15:35

Saulius Next

1,33010 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

George Over a year ago

This perfectly works. The key is the QueryHint, without which it expects two parameters. Thank you.

Collectives™ on Stack Overflow

Executing Oracle Stored Procedure with Hibernate

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related