-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
witcradg
 Post subject: Oracle Stored Proc throws invalid column index Exception
PostPosted: Mon Feb 25, 2013 6:47 pm 
Newbie

Joined: Mon Feb 25, 2013 6:06 pm
Posts: 2
In an effort to learn Hibernate (4.1) with Oracle Stored Procedures, I am trying to get an example to work that is as simple as I can make it:
1. No parameters passed in
2. SYS_REFCURSOR as the one return parameter

I'm getting an exception: "org.hibernate.exception.GenericJDBCException: Invalid column index" (see full stack trace below).
Searching for this online and experimenting with it for several days has been less than fruitful.
The Entity class works fine in other (non-stored procedure) contexts.
The stored procedure works fine when called from PL/SQL in Toad.
I am slightly concerned that the oracle portion of the stack trace indicates the setup is using the deprecated oracle.jdbc.driver classes when my setup is all for Oracle 10g.

Procedure
Code:
    CREATE OR REPLACE PROCEDURE IFC_OWNER.JPA_APPLICATION_R_TEST
    (    o_result_set OUT SYS_REFCURSOR  )
    AS
    BEGIN
        OPEN o_result_set FOR
        SELECT APPLICATION_ID, APP_COMMON_NM, APP_DESC, APP_URL FROM APPLICATION_R;
    END;
    /

xml call
Code:
    <sql-query name="oracleproccall" callable="true">
        <return alias="application_r" class="com.myco.entities.ApplicationR"/>
        <![CDATA[ call JPA_APPLICATION_R_TEST()   ]]>
    </sql-query>

java (Exception thrown at call to sqlQuery.getResultList())
Code:
public void run() {
   EntityManager entityManager = entityManagerFactory.createEntityManager();
   try {
      Query sqlQuery = entityManager.createNamedQuery("oracleproccall");
      List list = sqlQuery.getResultList();   
      <snip>   
   } catch (PersistenceException ex) {
      ex.printStackTrace();
   } finally {
      entityManager.close();
   }   
}

Entity class
Code:
/**
* ApplicationR generated by hbm2java
*/
@Entity
@Table(name="APPLICATION_R"
    ,schema="IFC_OWNER"
)
public class ApplicationR  implements java.io.Serializable {
     private int applicationId;
     private String appCommonNm;
     private String appDesc;
     private String appUrl;
     private Set<ApplicationFunctionR> applicationFunctionRs = new HashSet<ApplicationFunctionR>(0);

    public ApplicationR() {
    }
    public ApplicationR(int applicationId) {
        this.applicationId = applicationId;
    }
    public ApplicationR(int applicationId, String appCommonNm, String appDesc, String appUrl, Set<ApplicationFunctionR> applicationFunctionRs) {
       this.applicationId = applicationId;
       this.appCommonNm = appCommonNm;
       this.appDesc = appDesc;
       this.appUrl = appUrl;
       this.applicationFunctionRs = applicationFunctionRs;
    }
    @Id
    @Column(name="APPLICATION_ID", unique=true, nullable=false, precision=9, scale=0)
    public int getApplicationId() {
        return this.applicationId;
    }
    public void setApplicationId(int applicationId) {
        this.applicationId = applicationId;
    }
    @Column(name="APP_COMMON_NM", length=30)
    public String getAppCommonNm() {
        return this.appCommonNm;
    }
    public void setAppCommonNm(String appCommonNm) {
        this.appCommonNm = appCommonNm;
    }
    @Column(name="APP_DESC", length=50)
    public String getAppDesc() {
        return this.appDesc;
    }
    public void setAppDesc(String appDesc) {
        this.appDesc = appDesc;
    }
    @Column(name="APP_URL", length=100)
    public String getAppUrl() {
        return this.appUrl;
    }
    public void setAppUrl(String appUrl) {
        this.appUrl = appUrl;
    }
   @OneToMany(fetch=FetchType.LAZY, mappedBy="applicationR")
    public Set<ApplicationFunctionR> getApplicationFunctionRs() {
        return this.applicationFunctionRs;
    }
    public void setApplicationFunctionRs(Set<ApplicationFunctionR> applicationFunctionRs) {
        this.applicationFunctionRs = applicationFunctionRs;
    }
}

Exception
Quote:
Feb 25, 2013 2:42:49 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 17003, SQLState: 99999
Feb 25, 2013 2:42:49 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Invalid column index
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Invalid column index
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:266)
at com.myco.jpa.tests.ApplicationsByStoredProc.run(ApplicationsByStoredProc.java:36)
at com.myco.jpa.JPAEntities_InFactDomain_Test.main(JPAEntities_InFactDomain_Test.java:56)
Caused by: org.hibernate.exception.GenericJDBCException: Invalid column index
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.CallableStatementProxyHandler.continueInvocation(CallableStatementProxyHandler.java:49)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy16.registerOutParameter(Unknown Source)
at org.hibernate.dialect.Oracle8iDialect.registerResultSetOutParameter(Oracle8iDialect.java:507)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1732)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1696)
at org.hibernate.loader.Loader.doQuery(Loader.java:831)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:292)
at org.hibernate.loader.Loader.doList(Loader.java:2381)
at org.hibernate.loader.Loader.doList(Loader.java:2367)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2197)
at org.hibernate.loader.Loader.list(Loader.java:2192)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1784)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:229)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:257)
... 2 more
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.registerOutParameter(NewProxyCallableStatement.java:311)
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:597)


If I change the xml call to ? = call JPA_APPLICATION_R_TEST() per 4.1 doc section 18.2.2.1. Rules/limitations for using stored procedures
I get the following exception that doesn't seem like I'm on the right track at all:
ERROR: ORA-00900: invalid SQL statement
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:266)
Top
 Profile  
 
witcradg
 Post subject: Re: Oracle Stored Proc throws invalid column index Exception
PostPosted: Wed Mar 06, 2013 6:27 pm 
Newbie

Joined: Mon Feb 25, 2013 6:06 pm
Posts: 2
I found the issue. I was using a javax.persistence version of the Session and Query classes and not the org.hibernate version; javax.persistence does not support Oracle Stored Procedure calls while Hibernate does.
The difference is subtle without the import statements provided below.
The give away should have been the use of the sqlQuery.getResultList(). This is a JPA method call that does not exist in the Hibernate version of Query.

import import javax.persistence.Query;

Query sqlQuery = entityManager.createNamedQuery("oracleproccall");
List list = sqlQuery.getResultList();

The construct for using the Hibernate version is query.list() not query.getResultList():

Code:
   import java.util.Iterator;
   import java.util.List;
   import org.hibernate.Query;
   import org.hibernate.Session;
   import org.hibernate.SessionFactory;
   import org.hibernate.Transaction;
   import org.hibernate.cfg.Configuration;
   import com.myco.entities.ApplicationR
   
   
    private void anotherTest() {
        Configuration cfg = new Configuration();
        cfg.configure("hibernate.cfg.xml");
        SessionFactory sf = cfg.buildSessionFactory();
        Session s = sf.openSession();
        Transaction tx = s.beginTransaction();

        List<AppR> apps;
        Query namedQuery = s.getNamedQuery("oraclestoredproc");
        apps = namedQuery.list();
        apps = s.getNamedQuery("oraclestoredproc").list();
        Iterator<ApplicationR> iterator = apps.iterator();
        while (iterator.hasNext()) {
            ApplicationR app = (ApplicationR) iterator.next();
            System.out.print(app.getApplicationId() + " ");
            System.out.print(app.getAppCommonNm() + " ");
            System.out.print(app.getAppDesc() + " ");
            System.out.println(app.getAppUrl() + " ");
        }
        s.flush();
        tx.commit();
        s.close();
    }


Thanks to Jitu Ji and http://www.coderanch.com/t/466425/ORM/databases/Working-Hibernate-Call-Oracle-Stored

Note also: If you have an EntityManager object, you can get a org.hibernate.Session like this: Session s = entityManager.unwrap(Session.class);
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.