Stored Procedure for Querying does not work

sumeetsaxena · **Joined:** Tue Aug 23, 2005 7:40 am **Posts:** 2

I am attempting to call an Oracle function (that returns a SYS_REFCURSOR ) using the Halogen framework. The intent is to have the stored procedure return a collection of Halogen Objects.

Although I have exactly the same scenario as mentioned in the Chapter 17 of the Halogen progammer's reference, I am still unable to get get the program executed.

Everytime we run the program we get an SQLException mentioning 'invalid column name'. But I double-checked the Stored-Procedure and the HBM files, and could not find any discrepencies in either of them.
I even tried returning a single column, and writing the simplest of queries possible, but didn't get any success.

Hibernate version:3.0.5

Mapping documents:

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>
  <class name="com.m365.routing.model.OperatorNetwork" table="operator_network" dynamic-update="false" dynamic-insert="false">
    <id name="operatorNetworkId" column="operatornetworkid" type="java.lang.Long">
      <generator class="increment" />
    </id>
    <property name="mnc" type="java.lang.String" update="true" insert="true" column="mnc" />
    <property name="networkTypeId" type="java.lang.Long" update="true" insert="true" column="networktypeid" />
    <property name="se13NetworkCode" type="java.lang.String" update="true" insert="true" column="se13networkcode" />
    <property name="networkName" type="java.lang.String" update="true" insert="true" column="networkname" />
    <property name="ppcin" type="java.lang.String" update="true" insert="true" column="ppcin" />
    <property name="abbrnetName" type="java.lang.String" update="true" insert="true" column="abbrnetname" />
    <property name="networkStatusId" type="java.lang.Long" update="true" insert="true" column="networkstatusid" />
    <property name="sim" type="java.lang.String" update="true" insert="true" column="sim" />
    <property name="lastRemoteUpdate" type="java.util.Calendar" update="true" insert="true" column="lastremoteupdate" />
    <property name="createDate" type="java.util.Calendar" update="true" insert="true" column="createdate" />
    <property name="updateDate" type="java.util.Calendar" update="true" insert="true" column="updatedate" />
    <many-to-one name="country" class="com.m365.routing.model.Country" cascade="none" outer-join="auto" update="true" insert="true" column="countryid" />
    <many-to-one name="gsmOperator" class="com.m365.routing.model.GsmOperator" cascade="none" outer-join="auto" update="true" insert="true" column="operatorid" />
  </class>
  
 <sql-query name="selectAllOperatorNetworks_SP" callable="true">
        <return alias="optnet" class="com.m365.routing.model.OperatorNetwork">
           <return-property name="operatorNetworkId" column="opnid"/>
        </return>
        {?=call SelectAllOperatorNetworks()}
        </sql-query>
        
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

List list = session.getNamedQuery("selectAllOperatorNetworks_SP").list();

Full stack trace of any exception that occurs:

Code:

java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:305)
   at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3718)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2583)
   at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1617)
   at org.hibernate.type.LongType.get(LongType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at com.m365.routing.dao.OperatorNetworkDAO.getAll(OperatorNetworkDAO.java:66)
   at com.m365.routing.test.TestOperatorNetworkDAO.main(TestOperatorNetworkDAO.java:45)

Name and version of the database you are using:
Oracle 9.2

The generated SQL (show_sql=true):

Code:

Hibernate: {?=call SelectAllOperatorNetworks()}

Debug level Hibernate log excerpt:

Code:

20:41,190  INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:oracle:thin:@192.168.1.165:1521:ibank
20:41,242 DEBUG SessionImpl:250 - opened session at timestamp: 4607171957514240
20:41,273 DEBUG SessionImpl:1405 - SQL query: {?=call SelectAllOperatorNetworks()}
20:41,273 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
20:41,273 DEBUG ConnectionManager:296 - opening JDBC connection
20:41,273 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
20:41,273 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
20:41,273 DEBUG SQL:324 - {?=call SelectAllOperatorNetworks()}
Hibernate: {?=call SelectAllOperatorNetworks()}
20:41,273 DEBUG AbstractBatcher:378 - preparing statement
20:41,346 DEBUG AbstractBatcher:306 - about to open ResultSet (open ResultSets: 0, globally: 0)
20:41,346 DEBUG Loader:405 - processing result set
20:41,346 DEBUG Loader:410 - result set row: 0
20:41,367 DEBUG AbstractBatcher:313 - about to close ResultSet (open ResultSets: 1, globally: 1)
20:41,377 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
20:41,377 DEBUG AbstractBatcher:416 - closing statement
20:41,377 DEBUG JDBCExceptionReporter:63 - could not execute query [{?=call SelectAllOperatorNetworks()}]
java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)

max · **Posted:** Tue Aug 23, 2005 8:59 am

in 3.0.5 you need to list all columns via an return-property
in 3.1 just dont specify and alias and it will automatically use the column names used in your mappings.

max · **Posted:** Tue Aug 23, 2005 9:00 am

btw. what is Halogen framework ?

sumeetsaxena · **Joined:** Tue Aug 23, 2005 7:40 am **Posts:** 2

Sorry about the typo, I meant Hibernate not Halogen.

max wrote:

btw. what is Halogen framework ?

Halogen is another ORM framework (non-opensource) I worked on in my previous project. The name is still on the tip of my tongue (or... my fingertips). ;-)