13 Replies Latest reply: Jun 7, 2011 7:22 AM by gimbal2 RSS

    Problems of hibernate calling oracle stored procedure

    642232

      i'm using spring to manage hibernate, and i want to call a query stored procedure.

      the SP:
      create or replace
      procedure getEnabledSubSections(cur out sys_refcursor) as
      BEGIN
      open cur for select * from section;
      END GETENABLEDSUBSECTIONS;

      the program:
      public List getEnabledSubSections(){
      Object values[]={oracle.jdbc.OracleTypes.CURSOR};
      return hibernateTemplate.findByNamedQuery("getEnabledSubSections",values);
      }

      the xml mapping file:
      <sql-query name="getEnabledSubSections" callable="true">
      <return-property name="parentId" column="parentId" />
      <return-property name="avai" column="secAvai" />
      <return-property name="name" column="secName" />
      <return-property name="content" column="secCon" />
      <return-property name="creatorName" column="secCreName" />
      <return-property name="createDate" column="secCreDate" />
      <return-property name="creatorIp" column="secCreIP" />
      </return>
      { call getEnabledSubSections(?) }
      </sql-query>

      i have successfully run call the SP from JDBC. but i always get the exception using spring:
      Caused by: org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value
      at org.hibernate.dialect.Oracle9Dialect.registerResultSetOutParameter(Oracle9Dialect.java:268)
      at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1574)
      at org.hibernate.loader.Loader.doQuery(Loader.java:661)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
      at org.hibernate.loader.Loader.doList(Loader.java:2145)
      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:1655)
      at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
      at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(HibernateTemplate.java:916)
      at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
      at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:907)
      at zzuli.tv.orm.dao.impl.SectionDaoImpl.getEnabledSubSections(SectionDaoImpl.java:192)
      at SectionDaoTest.main(SectionDaoTest.java:16)
      Caused by: java.lang.IllegalAccessException: Class org.hibernate.dialect.Oracle9Dialect can not access a member of class oracle.jdbc.driver.OracleTypes with modifiers ""
      at sun.reflect.Reflection.ensureMemberAccess(Unknown Source)
      at java.lang.Class.newInstance0(Unknown Source)
      at java.lang.Class.newInstance(Unknown Source)
      at org.hibernate.dialect.Oracle9Dialect.registerResultSetOutParameter(Oracle9Dialect.java:266)
      ... 15 more

      i have googled the problems for days and searched hibernate docs,but got no definite answers.
      i'm using oracle 11g, hibernate3.1 , spring 2.0
      please anyone could help me? thanks

        • 1. Re: Problems of hibernate calling oracle stored procedure
          Avi Abrami
          MyCoy,
          It's been a couple of days and no-one has answered so I'll take a guess.
          I see from your stack trace you are referencing package "oracle.jdbc.driver".
          This package has been deprecated.
          Perhaps this is your problem?
          Class "OracleTypes" should be in the JDBC driver JAR file.
          Have you checked it is there and in the same package as you are referencing?
          And have you checked that Hibernate configured is using the correct JDBC driver JAR file?

          Good Luck,
          Avi.
          • 2. Re: Problems of hibernate calling oracle stored procedure
            642232
            to Avi Abrami :
            yes,i'm sure that class "OracleTypes" is in the JDBC driver JAR file, and i have succeeded called the SP from JDBC by
            registering an "oracle.jdbc.OracleTypes.CURSOR" out parameter.
            but you said that package is deprecated,i'll find some new and try again. thanks
            • 3. Re: Problems of hibernate calling oracle stored procedure
              428263
              Just to clarify. As Avi pointed out to me once before, the oracle.jdbc.driver package has been deprecated, but the oracle.jdbc package has not. It is completely valid to refer to oracle.jdbc.OracleTypes as you've done in your program.

              Object values[]={*oracle.jdbc.OracleTypes.CURSOR*};

              So it seems strange that the stack trace would mention oracle.jdbc.driver.OracleTypes instead.
              • 4. Re: Problems of hibernate calling oracle stored procedure
                642232
                to Avi Abrami:
                i have tried the latest jdbc driver, ojdbc6_g.jar, for oracle 11g and jdk6. but the exeception remains.
                you have said that package has been deprecated, what does that exactly mean?
                thanks
                • 5. Re: Problems of hibernate calling oracle stored procedure
                  Avi Abrami
                  MyCoy wrote:
                  you have said that package has been deprecated, what does that exactly mean?
                  http://forums.oracle.com/forums/ann.jspa?annID=201

                  Good Luck,
                  Avi.
                  • 6. Re: Problems of hibernate calling oracle stored procedure
                    jvillavi
                    The problem:
                    I had the same problem trying to call a function that was returning a REF CURSOR...

                    The solution (don't use deprecated classes):
                    Just make sure you're using the correct version of jdbc driver and change the connection.driver_class property in your hibernate config file to oracle.jdbc.OracleDriver (it worked to me using hibernate 3, oracle 10.2.0.10 and ojdbc14.jar thin jdbc driver.
                    • 7. Re: Problems of hibernate calling oracle stored procedure
                      699225
                      jvillavi wrote:

                      The solution (don't use deprecated classes):
                      Just make sure you're using the correct version of jdbc driver and change the connection.driver_class property in your hibernate config file to oracle.jdbc.OracleDriver (it worked to me using hibernate 3, oracle 10.2.0.10 and ojdbc14.jar thin jdbc driver.
                      You have the correct solution, but the problem is actually that Hibernate's Oracle8iDialect class is explicitly loading oracle.jdbc.driver.OracleTypes, by name. Hibernate Core 3.3.2 GA has a fix for this problem.

                      - Jack
                      • 8. Re: Problems of hibernate calling oracle stored procedure
                        744543
                        Hello, This thread is a bit old, but I recently experience the same problem as mentioned here.
                        I'm using hibernate 3.0, WLS 10. We upgrade from WLS 9.1-> 10 and the problem occured.
                        After the update a stored procedure returning a reference cursor/result set now gets this error:


                        org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value
                        at org.hibernate.dialect.Oracle9Dialect.registerResultSetOutParameter(Oracle9Dialect.java:268)
                        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1574)
                        at org.hibernate.loader.Loader.doQuery(Loader.java:661)
                        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
                        at org.hibernate.loader.Loader.doList(Loader.java:2145)
                        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:1655)
                        at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
                        at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
                        Caused by: java.lang.IllegalAccessException: Class org.hibernate.dialect.Oracle9Dialect can not access a member of class oracle.jdbc.driver.OracleType
                        s with modifiers ""
                        at sun.reflect.Reflection.ensureMemberAccess(Reflection.java:65)
                        at java.lang.Class.newInstance0(Class.java:349)
                        at java.lang.Class.newInstance(Class.java:308)
                        at org.hibernate.dialect.Oracle9Dialect.registerResultSetOutParameter(Oracle9Dialect.java:266)
                        ... 36 more


                        I tried to follow the suggestion of getting hibernate core 3.3.2 GA jar and it give me this error on start up on my application:
                        PLEASE HELP!

                        java.lang.NoClassDefFoundError: org/slf4j/LoggerFactory
                        at org.hibernate.cfg.Configuration.<clinit>(Configuration.java:152)
                        at com.emergis.eHealth.morpheus.database.HibernateUtil.<clinit>(HibernateUtil.java:32)
                        at com.emergis.eHealth.morpheus.database.logging.DefaultSessionService.openSession(DefaultSessionService.java:16)
                        at com.emergis.eHealth.morpheus.database.logging.HibernateAppender.append(HibernateAppender.java:107)
                        at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:221)
                        at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:57)
                        at org.apache.log4j.Category.callAppenders(Category.java:187)
                        at org.apache.log4j.Category.forcedLog(Category.java:372)
                        at org.apache.log4j.Category.info(Category.java:674)
                        at com.emergis.eHealth.morpheus.servlets.SetupServlet.initLogging(SetupServlet.java:109)
                        at com.emergis.eHealth.morpheus.servlets.SetupServlet.init(SetupServlet.java:56)
                        at weblogic.servlet.internal.StubSecurityHelper$ServletInitAction.run(StubSecurityHelper.java:283)
                        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
                        at weblogic.security.service.SecurityManager.runAs(Unknown Source)
                        at weblogic.servlet.internal.StubSecurityHelper.createServlet(StubSecurityHelper.java:64)
                        at weblogic.servlet.internal.StubLifecycleHelper.createOneInstance(StubLifecycleHelper.java:58)
                        at weblogic.servlet.internal.StubLifecycleHelper.<init>(StubLifecycleHelper.java:48)
                        at weblogic.servlet.internal.ServletStubImpl.prepareServlet(ServletStubImpl.java:521)
                        at weblogic.servlet.internal.WebAppServletContext.preloadServlet(WebAppServletContext.java:1893)
                        at weblogic.servlet.internal.WebAppServletContext.loadServletsOnStartup(WebAppServletContext.java:1870)
                        at weblogic.servlet.internal.WebAppServletContext.preloadResources(WebAppServletContext.java:1790)
                        at weblogic.servlet.internal.WebAppServletContext.start(WebAppServletContext.java:2999)
                        at weblogic.servlet.internal.WebAppModule.startContexts(WebAppModule.java:1371)
                        at weblogic.servlet.internal.WebAppModule.start(WebAppModule.java:468)
                        at weblogic.application.internal.flow.ModuleStateDriver$3.next(ModuleStateDriver.java:204)
                        at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:37)
                        at weblogic.application.internal.flow.ModuleStateDriver.start(ModuleStateDriver.java:60)
                        at weblogic.application.internal.flow.ScopedModuleDriver.start(ScopedModuleDriver.java:200)
                        at weblogic.application.internal.flow.ModuleListenerInvoker.start(ModuleListenerInvoker.java:117)
                        at weblogic.application.internal.flow.ModuleStateDriver$3.next(ModuleStateDriver.java:204)
                        at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:37)
                        at weblogic.application.internal.flow.ModuleStateDriver.start(ModuleStateDriver.java:60)
                        at weblogic.application.internal.flow.StartModulesFlow.activate(StartModulesFlow.java:27)
                        at weblogic.application.internal.BaseDeployment$2.next(BaseDeployment.java:635)
                        at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:37)
                        at weblogic.application.internal.BaseDeployment.activate(BaseDeployment.java:212)
                        at weblogic.application.internal.EarDeployment.activate(EarDeployment.java:16)
                        at weblogic.application.internal.DeploymentStateChecker.activate(DeploymentStateChecker.java:162)
                        at weblogic.deploy.internal.targetserver.AppContainerInvoker.activate(AppContainerInvoker.java:79)
                        at weblogic.deploy.internal.targetserver.BasicDeployment.activate(BasicDeployment.java:184)
                        at weblogic.deploy.internal.targetserver.BasicDeployment.activateFromServerLifecycle(BasicDeployment.java:361)
                        at weblogic.management.deploy.internal.DeploymentAdapter$1.doActivate(DeploymentAdapter.java:51)
                        at weblogic.management.deploy.internal.DeploymentAdapter.activate(DeploymentAdapter.java:196)
                        at weblogic.management.deploy.internal.AppTransition$2.transitionApp(AppTransition.java:30)
                        at weblogic.management.deploy.internal.ConfiguredDeployments.transitionApps(ConfiguredDeployments.java:233)
                        at weblogic.management.deploy.internal.ConfiguredDeployments.activate(ConfiguredDeployments.java:169)
                        at weblogic.management.deploy.internal.ConfiguredDeployments.deploy(ConfiguredDeployments.java:123)
                        at weblogic.management.deploy.internal.DeploymentServerService.resume(DeploymentServerService.java:173)
                        at weblogic.management.deploy.internal.DeploymentServerService.start(DeploymentServerService.java:89)
                        at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
                        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
                        at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
                        Caused by: java.lang.ClassNotFoundException: org.slf4j.LoggerFactory
                        at weblogic.utils.classloaders.GenericClassLoader.findLocalClass(GenericClassLoader.java:283)
                        at weblogic.utils.classloaders.GenericClassLoader.findClass(GenericClassLoader.java:256)
                        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
                        at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
                        at weblogic.utils.classloaders.GenericClassLoader.loadClass(GenericClassLoader.java:176)
                        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
                        ... 52 more

                        thanks in advance,
                        • 9. Re: Problems of hibernate calling oracle stored procedure
                          782874
                          Hi, I to have the same problem did any one got the solution for this. If you have please post it. Thank you all in advance.
                          • 10. Re: Problems of hibernate calling oracle stored procedure
                            782874
                            Hi All, I found the solution but dont know how far if will work for u all. I tried to connect to oracle 11g with oracle9 dialect and used ojdbc14.jar and it is working fine to call the pl/sql functions. check it out.
                            • 11. Re: Problems of hibernate calling oracle stored procedure
                              865545
                              Hi ,

                              I am also getting "org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR " in my application.

                              Settings:
                              We are using hibernet3.2.1, jboss 4.2.3 cp 09, jdk1.5, Oracle10G and 11G
                              Driver: ojdbc5 and Oracle9Dialect

                              Our app configured to access 10G as well as 11G DB instance. We are using stored proc which returns ref cursor in our application which points to 10G DB. Application was working fine till now.

                              Recently DB team applied 10.2.0.5 Patch on DB server which has both 11G and 10G DB instance. After that we are getting OracleTypes.CURSOR error for application which is pointing to 10G DB.

                              I went through following link which talks about 11G
                              http://opensource.atlassian.com/projects/hibernate/browse/HHH-3159
                              All it says about 11G, where as we are getting issue while pointing to 10G DB.

                              Not sure do we need to do any changes on app server regarding upgrading hibernate jar or oracle client (currently using 10G client). Any suggestion on this? Any particular settings at DB side which could avoid configuration changes at application side?

                              Thanks in Advance


                              Sucheta
                              • 13. Re: Problems of hibernate calling oracle stored procedure
                                gimbal2
                                862542 wrote:
                                We are using hibernet3.2.1, jboss 4.2.3 cp 09, jdk1.5, Oracle10G and 11G
                                JBoss 4.2.3 comes with Hibernate 3.2.6 out of the box, but the thread states that Hibernate 3.3.2 has a fix for it. I have upgraded Hibernate in Jboss 4.2.3 without any issues in the past, so you can try it. You can find the Hibernate libraries in the lib directory of your server instance. You will have to figure out from the documentation which libraries you have to replace exactly, but it will probably be:

                                hibernate3.jar
                                hibernate-annotations.jar
                                hibernate-entitymanager.jar
                                cglib.jar
                                antlr.jar
                                javassist.jar


                                Of course, make a copy of your existing instance and try the upgrade in that copy first. Also note that you should take the last version of Hibernate 3.3.X (the JPA 1.0 releases), do not go as far as upgrading to Hibernate 3.5 or higher (the JPA 2.0 releases).


                                Also for the next time: create a new thread in stead of resurrecting an old one. If you really have to, create a link to a thread that describes a similar problem.