title: executing stored procedure from Spring-Hibernate using Annotations
body: <p>I'm trying to execute a simple stored procedure with Spring/Hibernate using Annotations.
  Here are my code snippets:</p>
  
  <p>DAO class:</p>
  
  <pre><code>    public class UserDAO extends HibernateDaoSupport {
  
          public  List selectUsers(final String eid){
              return (List) getHibernateTemplate().execute(new HibernateCallback() {
                    public Object doInHibernate(Session session) throws 
                     HibernateException, SQLException
                    {
                        Query q = session.getNamedQuery("SP_APPL_USER");
                        System.out.println(q);
                        q.setString("eid", eid);
                        return q.list();
                    }
                });
            }
      }
  </code></pre>
  
  <p>my entity class:</p>
  
  <pre><code> @Entity
      @Table(name = "APPL_USER")
      @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
      @DiscriminatorFormula(value = "SUBSCRIBER_IND")
      @DiscriminatorValue("N")
      @NamedQuery(name = "req.all", query = "select n from Requestor n")
      @org.hibernate.annotations.NamedNativeQuery(name = "SP_APPL_USER", 
  query = "call SP_APPL_USER(?, :eid)", callable = true, readOnly = true, resultClass = Requestor.class)
  
      public class Requestor {
  
      @Id
          @Column(name = "EMPL_ID")
          public String getEmpid() {
              return empid;
          }
  
          public void setEmpid(String empid) {
              this.empid = empid;
          }
  
          @Column(name = "EMPL_FRST_NM")
          public String getFirstname() {
              return firstname;
          }
      ...
      }
  
      public class Test {
          public static void main(String[] args) {
              ApplicationContext ctx = new ClassPathXmlApplicationContext(
                      "applicationContext.xml");
              APFUser user = (APFUser)ctx.getBean("apfUser");
  
              List selectUsers = user.getUserDAO().selectUsers("EMP456");
              System.out.println(selectUsers);
          }
  
      }
  </code></pre>
  
  <p>and the stored procedure:</p>
  
  <pre><code>create or replace PROCEDURE SP_APPL_USER (p_cursor out sys_refcursor, eid in varchar2)
  as
    empId varchar2(8);  
    fname varchar2(50); 
    lname varchar2(50); 
  begin
    empId := null;  
    fname := null;  
    lname := null;  
    open p_cursor for
      select l.EMPL_ID, l.EMPL_FRST_NM, l.EMPL_LST_NM
        into empId, fname, lname
        from APPL_USER l
       where l.EMPL_ID = eid;
  end;
  </code></pre>
  
  <p>If i enter invalid EID, its returning empty list which is OK.</p>
  
  <p>But when record is there, following exception is thrown:</p>
  
  <pre><code>Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [call SP_APPL_USER(?, ?)]; nested exception is java.sql.SQLException: Invalid column name
  </code></pre>
  
  <p>Do I need to modify the entity(Requestor.class) ?
  How will the REFCURSOR be converted to the List?
  The stored procedure is expected to return more than one record.</p>