call pl/sql procedure in oracle from Hibernate with a return parameter

Question

I have a PL/SQL function , this function return two parameters : req_type and req_seq.

this is the code of PL/SQL.

declare
    req_type number;
    req_seq  number;

    TEST_PKG.insert_req(001,req_type,req_seq );
    dbms_output.put_line('req_type='||req_type);
    dbms_output.put_line('req_seq='||req_seq);
end;

I need to call this function from Hibernate.

I didn't find the exact way to do this work from hibernate

I tried yo use this code :

public EmpEntity insertReq(String numEmp) {
    String query = " call TEST_PKG.insert_req(" + numEmp + ",req_type,req_seq ) " ;

    SQLQuery sqlQuery = this.getSession().createSQLQuery(query);
    sqlQuery.executeUpdate();

    sqlQuery.setResultTransformer(Transformers.aliasToBean(
            EmpEntity.class));

    List<EmpEntity> list = sqlQuery.list();

    EmpEntity empEntity=list.get(0);
    empEntity.setRequestType(.....);
    empEntity.setRequestSec(....);

    return empEntity;
}

You Pl/Sql is invalid. There's a BEGIN missing

kara
– kara

2018-10-29 10:13:35 +00:00
Commented Oct 29, 2018 at 10:13 — kara
– kara, Commented Oct 29, 2018 at 10:13

greengreyblue · Accepted Answer · 2018-10-29 10:08:46Z

1

You may try the below. Here we are setting in and out parameters before execution.

StoredProcedureQuery query = entityManager
        .createStoredProcedureQuery("proc_name")
        .registerStoredProcedureParameter(1, Long.class, 
            ParameterMode.IN)
        .registerStoredProcedureParameter(2, Long.class, 
            ParameterMode.OUT);

    query.execute();

    Long res = (Long) query.getOutputParameterValue(2);

You can replace You can find docs for StoredProcedureQuery here https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/StoredProcedureQuery.html

answered Oct 29, 2018 at 10:08

greengreyblue

3893 silver badges12 bronze badges

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

1 Comment

franco Over a year ago

thank you for your response, the problem is that I used hibernate3.jar so I cannot use StoredProcedureQuery. I hope there is a solution using hibernate3

Collectives™ on Stack Overflow

call pl/sql procedure in oracle from Hibernate with a return parameter

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