title: Retrieving a value from Stored Procedure using Native SQL Hibernate
body: <p>Below is the stored procedure:</p>
  
  <pre><code>create or replace procedure 
  proc_emp_name(v_emp out emp.emp_name%TYPE, v_empid in emp.emp_id%TYPE)
  is
  begin
  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!!!');
  end;
  </code></pre>
  
  <p>I want to invoke this using Native SQL, followed this link but not sure how to retrieve the <code>OUT</code> parameter from the Procedure.</p>
  
  <p><a href="http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/" rel="nofollow">http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/</a></p>
  
  <p>Kindly let me know the simplest way to invoke the procedure and get the results out of it.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>As suggested, checking the docs, I modified the Proc having first parameter as a <code>SYS_REFCURSOR</code> as follows:</p>
  
  <pre><code>create or replace procedure
  proc_empname_refcursor(v_empname OUT SYS_REFCURSOR, v_deptid in emp.emp_id%type)
  is 
  begin
  open v_empname for select * from dept where dept_id = v_deptid;
  end;
  </code></pre>
  
  <p>I am able to invoke it using <code>NamedQuery</code> 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 <code>NamedQuery</code> but it did not worked out:</p>
  
  <pre><code>Query query = session.createSQLQuery(
                  "CALL proc_empname_refcursor(?, :deptId)")
                  .addEntity(Dept.class)
                  .setParameter("deptId", new Integer(2));
  
              List&lt;Dept&gt; departments = query.list();
              for(int i=0; i&lt;departments.size(); i++){
                  Dept department = (Dept)departments.get(i);
                  System.out.println("Dept Id: " + department.getDeptId());
                  System.out.println("Dept Name: " + department.getDeptName());
              }
  </code></pre>
  
  <p>I am getting the exception:</p>
  
  <pre><code>org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL proc_empname_refcursor(?, :deptId)]
  at     org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:319)
  at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:201)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:145)
  at com.jdbc.HibernateStartup.main(HibernateStartup.java:70)
  </code></pre>
  
  <p>Kindly let me know how to resolve this.</p>