Invalid column name ref cursor stored procedure error

Madlax · **Joined:** Fri Oct 28, 2005 11:49 pm **Posts:** 5

Hi

Getting a invalid column name error. As far as I can tell everything is fine.
Any suggestions would be very much appreciated.

Hibernate version: 3.1.2

Mapping documents:

Code:

<sql-query name="selectAllBuUits_SP" callable="true">
  <return class="com.encana.rtd.dao.to.BuTO">
    <return-property name="buId" column="bu_id"/>
    <return-property name="buName" column="bu_name"/>            
  </return>
  { ? = call rtd.rtd_int.get_bu() }
</sql-query>

Return Class:

Code:

package com.encana.rtd.dao.to;

import java.io.Serializable;
import java.util.Date;
import java.util.Set;

public class BuTO implements Serializable {
   private Long   buId;
   private String   buName;
   private String   buStatus;
   private Date   lastModDt;
    private Set     sbus;

   public BuTO() {}
   
   public BuTO(Long buId, String buName) {
      this.buId = buId;
      this.buName = buName;
   }

   public BuTO(Long buId, String buName,Set sbus) {
      this.buId = buId;
      this.buName = buName;
      this.sbus = sbus;
   }
   
   public Set getSbus() {
      return sbus;
   }

   public void setSbus(Set sbus) {
      this.sbus = sbus;
   }

   public Long getBuId() {
      return buId;
   }
   public void setBuId(Long buId) {
      this.buId = buId;
   }
   public String getBuName() {
      return buName;
   }
   public void setBuName(String buName) {
      this.buName = buName;
   }
   public String getBuStatus() {
      return buStatus;
   }
   public void setBuStatus(String buStatus) {
      this.buStatus = buStatus;
   }
   public Date getLastModDt() {
      return lastModDt;
   }
   public void setLastModDt(Date lastModDt) {
      this.lastModDt = lastModDt;
   }

}

Stored procedure:

Code:

CREATE OR REPLACE PACKAGE rtd_int AS   
   FUNCTION get_bu RETURN SYS_REFCURSOR;
END;
/

CREATE OR REPLACE PACKAGE BODY rtd_int AS

   FUNCTION get_bu RETURN SYS_REFCURSOR IS
      bu_cv SYS_REFCURSOR;
   BEGIN
      OPEN bu_cv FOR
      SELECT bu_id, bu_name FROM bus_units
       WHERE bu_status = 'A'
       ORDER BY bu_name;
      RETURN bu_cv;
   END;
END;
/

Full stack trace of any exception that occurs:

Code:

com.encana.esrp.bo.exception.EsrpBOException: com.encana.dao.exception.DAOException: org.hibernate.exception.GenericJDBCException: could not execute query
   at com.encana.esrp.bo.impl.RtdBuBO.getBuCodes(Unknown Source)
   at com.encana.esrp.ui.action.RtdWellSearchAction.setFormProperties(RtdWellSearchAction.java:118)
   at com.encana.esrp.ui.action.RtdWellSearchAction.actionRead(RtdWellSearchAction.java:192)
   at com.encana.esrp.ui.action.EsrpDispatchAction.read(EsrpDispatchAction.java:103)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
   at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
   at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1006)
   at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:419)
   at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:315)
   at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6718)
   at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
   at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
   at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3764)
   at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2644)
   at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:219)
   at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:178)
Caused by: com.encana.dao.exception.DAOException: org.hibernate.exception.GenericJDBCException: could not execute query
   at com.encana.dao.HibernateDAOBase.namedRetrieve(Unknown Source)
   at com.encana.rtd.dao.impl.BuDAO.retrieveCodes(Unknown Source)
   ... 26 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2148)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
   ... 28 more
Caused by: java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
   at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3295)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914)
   at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:1661)
   at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getTimestamp(Unknown Source)
   at org.hibernate.type.TimestampType.get(TimestampType.java:30)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:1899)
   at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1372)
   at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1300)
   at org.hibernate.loader.Loader.getRow(Loader.java:1197)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:569)
   at org.hibernate.loader.Loader.doQuery(Loader.java:689)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   ... 34 more

Name and version of the database you are using: Oracle 9.2.0.1
drivers: 10.2.0.1.0

The generated SQL (show_sql=true):
Hibernate: { ? = call rtd.rtd_int.get_bu() }

Debug level Hibernate log excerpt:

Code:

2006-04-19 15:25:31,302 DEBUG org.hibernate.engine.query.QueryPlanCache:117  - located native-sql query plan in cache ({ ? = call rtd.rtd_int.get_bu() })
2006-04-19 15:25:31,302 DEBUG org.hibernate.impl.SessionImpl:1664  - SQL query: { ? = call rtd.rtd_int.get_bu() }
2006-04-19 15:25:31,302 DEBUG org.hibernate.jdbc.AbstractBatcher:311  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-19 15:25:31,302 DEBUG org.hibernate.jdbc.ConnectionManager:358  - opening JDBC connection
2006-04-19 15:25:31,302 DEBUG org.hibernate.SQL:346  - { ? = call rtd.rtd_int.get_bu() }
2006-04-19 15:25:31,302 DEBUG org.hibernate.jdbc.AbstractBatcher:424  - preparing statement
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:327  - about to open ResultSet (open ResultSets: 0, globally: 0)
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:682  - processing result set
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:687  - result set row: 0
2006-04-19 15:25:31,317 DEBUG org.hibernate.type.LongType:123  - returning '30' as column: bu_id
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:1164  - result row: EntityKey[com.encana.rtd.dao.to.BuTO#30]
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:1347  - Initializing object from ResultSet: [com.encana.rtd.dao.to.BuTO#30]
2006-04-19 15:25:31,317 DEBUG org.hibernate.persister.entity.AbstractEntityPersister:1860  - Hydrating entity: [com.encana.rtd.dao.to.BuTO#30]
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:334  - about to close ResultSet (open ResultSets: 1, globally: 1)
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:319  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:470  - closing statement
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.ConnectionManager:341  - aggressively releasing JDBC connection
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.ConnectionManager:378  - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]

Madlax · **Joined:** Fri Oct 28, 2005 11:49 pm **Posts:** 5

Hi

Ok, here is what the problem is.
There are other attributes in the BuTO mapping file. HIbernate is trying to pull those in from the result set, even though they are not defined in the
<sql-query mapping.

This is a bug for sure.
Even without the return-property mappings, hibernate is still trying to get columns that are in the mapping file but not the result set.

Mabye I am missing something, but I cannot see what it would be.

Code:

<sql-query name="selectAllBuUits_SP" callable="true"> 
  <return class="com.encana.rtd.dao.to.BuTO"> 
    <return-property name="buId" column="bu_id"/> 
    <return-property name="buName" column="bu_name"/>            
  </return> 
  { ? = call rtd.rtd_int.get_bu() } 
</sql-query> 

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="true" package="com.encana.rtd.dao.to">
   <class name="BuTO" table="rtd.bus_units">
      <id name="buId" column="bu_id" type="long" unsaved-value="null">
         <generator class="native"/>
      </id>

      <timestamp name="lastModDt" column="last_mod_dt"/>

      <property name="buName" column="bu_name" type="string" not-null="true" length="25"/>
      <property name="buStatus" column="bu_status" type="string" not-null="true" length="1"/>
      
      <set
            name="sbus"
            lazy="false"
            inverse="true"
            cascade="none"
            sort="unsorted"
            order-by="SBU_NAME">
            <key column="BU_ID"/>
            <one-to-many class="com.encana.rtd.dao.to.SbusTO"/>
        </set>
   
   
   </class>
</hibernate-mapping>

max · **Posted:** Thu Apr 20, 2006 2:56 pm

how can it be a bug when you tell hibernate to return a BuTO class ?
That of course require us to get all the columns for it, otherwise it would be a partial object and not something that would be persistent.

If you want partial objects returned from <sql-query> use hibernate 3.2 from svn with resulttransformer (see our blog about how to use it)

Madlax · **Joined:** Fri Oct 28, 2005 11:49 pm **Posts:** 5

Hi

I understand your point. In this case, we are not looking to get something that is going to be persisted, but instead a listing to build a drop down control.

Maybe you can tell me what the purpose of the return-property is for. I assumed it was meant to emulate something like

select new BuTO(bu.buId,bubuName) from BuTO bu

Code:

<sql-query name="selectAllBuUits_SP" callable="true"> 
  <return class="com.encana.rtd.dao.to.BuTO"> 
    <return-property name="buId" column="bu_id"/> 
    <return-property name="buName" column="bu_name"/>            
  </return> 
  { ? = call rtd.rtd_int.get_bu() } 
</sql-query> 

Thanks,
Brent Parsons

max · **Posted:** Fri Apr 21, 2006 1:36 am

no, select new BuTO(x,y) from ... for native sql have not been possible until I added resulttransformer support (see http://blog.hibernate.org/cgi-bin/blosx ... ql_and_sql)

<return-property> is simply their to allow you to redefine how a property is going to be retreived. e.g. use different column aliases than what is mapped.