user182944 user182944 - 1 year ago 108
Java Question

Retrieving a value from Stored Procedure using Native SQL Hibernate

Below is the stored procedure:

create or replace procedure
proc_emp_name(v_emp out emp.emp_name%TYPE, v_empid in emp.emp_id%TYPE)
select emp_name into v_emp from emp where emp_id = v_empid;
dbms_output.put_line('Emp Name: ' || v_emp);
dbms_output.put_line('Procedure created successfully!!!');

I want to invoke this using Native SQL, followed this link but not sure how to retrieve the
parameter from the Procedure.

Kindly let me know the simplest way to invoke the procedure and get the results out of it.


As suggested, checking the docs, I modified the Proc having first parameter as a
as follows:

create or replace procedure
proc_empname_refcursor(v_empname OUT SYS_REFCURSOR, v_deptid in emp.emp_id%type)
open v_empname for select * from dept where dept_id = v_deptid;

I am able to invoke it using
but I don't want to add anything in the mapping files because of some other restrictions. I tried the below code for invoking the proc without using
but it did not worked out:

Query query = session.createSQLQuery(
"CALL proc_empname_refcursor(?, :deptId)")
.setParameter("deptId", new Integer(2));

List<Dept> departments = query.list();
for(int i=0; i<departments.size(); i++){
Dept department = (Dept)departments.get(i);
System.out.println("Dept Id: " + department.getDeptId());
System.out.println("Dept Name: " + department.getDeptName());

I am getting the exception:

org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL proc_empname_refcursor(?, :deptId)]
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(
at org.hibernate.impl.SQLQueryImpl.verifyParameters(
at org.hibernate.impl.SQLQueryImpl.list(
at com.jdbc.HibernateStartup.main(

Kindly let me know how to resolve this.

Answer Source

I've managed to get an out parameter from a stored procedure using the following code in Hibernate and MS SQL Server:

public Serializable generate(SessionImplementor session, Object object) throws HibernateException {
    Connection connection = session.connection();
    CallableStatement callable = null;
    try {
        callable = connection.prepareCall("execute [procedure] ?");
        callable.registerOutParameter(1, Types.INTEGER);
        int id = callable.getInt(1);

        return id;
    } catch (SQLException e) {
    } finally {
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download