-->
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 » NHibernate 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
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
dionysos1973
 Post subject: Oracle stored procedure with ref cursor still fails
PostPosted: Thu Jul 19, 2007 7:16 am 
Newbie

Joined: Thu Jul 19, 2007 6:49 am
Posts: 13
I've been reading virtually any topic with the word stored procedure in it. I have finally managed to execute Oracle functions, but somehow, executing a stored procedure with a REF_CURSOR as its first OUT parameter (as stated in the docs), always fails. The syntax to use differs between the topics. I've seen the following:
    * exec sp_myprocedure
    * call sp_myprocedure
    * { ? = call sp_myprocedure() }

I also found a related issue in JIRA (http://jira.nhibernate.org/browse/NH-847). Unfortunately this bug has been open since December 2006.

Hibernate version:
Nhibernate.dll - 1.2.0.4000 (1.2GA)
Oracle.DataAccess - 2.102.2.20

Mapping documents:
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
<property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>
<property name="connection.connection_string">Data Source=Hpowkdcr;Persist Security Info=True;User ID=xxxx;Password=xxxxx;</property>
<property name="show_sql">true</property>
<property name="use_outer_join">true</property>
<property name="hibernate.query.substitutions">true=J, no=N</property>
</session-factory>
</hibernate-configuration>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="TestClient" assembly="TestClient">
<sql-query name="TestRefProcedure">
<return-scalar column="ID" type="Int64"/>
exec dtn_adapter_ppe.test_ref
</sql-query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
IList list = session.GetNamedQuery("TestRefProcedure").List();

Full stack trace of any exception that occurs:
NHibernate.ADOException was unhandled
Message="could not execute query\r\n[ exec dtn_adapter_ppe.test_ref ]\r\n[SQL: exec dtn_adapter_ppe.test_ref]"
Source="NHibernate"
StackTrace:
bij NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
bij NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
bij NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
bij NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
bij NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
bij NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
bij NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
bij NHibernate.Impl.SqlQueryImpl.List()
bij TestClient.Program.Main(String[] args) in D:\Projects\Dyktion\Dyktion\Test\TestClient\Program.cs:regel 45
bij System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
bij System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
bij Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
bij System.Threading.ThreadHelper.ThreadStart_Context(Object state)
bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
bij System.Threading.ThreadHelper.ThreadStart()

ORA-00900: invalid SQL statement
Message="could not execute query\r\n[ exec dtn_adapter_ppe.test_ref ]\r\n[SQL: exec dtn_adapter_ppe.test_ref]"
Source="NHibernate"
Stacktrace:
bij NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
bij NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
bij NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
bij NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
bij NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
bij NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
bij NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
bij NHibernate.Impl.SqlQueryImpl.List()
bij TestClient.Program.Main(String[] args) in D:\Projects\Dyktion\Dyktion\Test\TestClient\Program.cs:regel 45
bij System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
bij System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
bij Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
bij System.Threading.ThreadHelper.ThreadStart_Context(Object state)
bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
bij System.Threading.ThreadHelper.ThreadStart()

Name and version of the database you are using:
Oracle 10g

The generated SQL (show_sql=true):
exec dtn_adapter_ppe.test_ref

Debug level Hibernate log excerpt:
[2007-07-19 13:15:06.70 P5468 T6068 Debug NHibernateSessionManager ] Opened session (hashcode 58d7fb)
[2007-07-19 13:15:06.72 P5468 T6068 Debug NHibernate ] starting processing of sql query [exec dtn_adapter_ppe.test_ref]
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] flushing session
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] Flushing entities and processing referenced collections
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] Processing unreferenced collections
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] scheduling collection removes/(re)creates/updates
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
[2007-07-19 13:15:06.73 P5468 T6068 Debug NHibernate ] dont need to execute flush
[2007-07-19 13:15:06.78 P5468 T6068 Debug NHibernate ] Opened new IDbCommand, open IDbCommands: 1
[2007-07-19 13:15:06.78 P5468 T6068 Debug NHibernate ] Building an IDbCommand object for the SqlString: exec dtn_adapter_ppe.test_ref
[2007-07-19 13:15:06.78 P5468 T6068 Information NHibernate ] exec dtn_adapter_ppe.test_ref
[2007-07-19 13:15:06.78 P5468 T6068 Debug NHibernate ] exec dtn_adapter_ppe.test_ref
[2007-07-19 13:15:06.78 P5468 T6068 Debug NHibernate ] Obtaining IDbConnection from Driver
[2007-07-19 13:15:07.01 P5468 T6068 Debug NHibernate ] SQL Exception
[2007-07-19 13:15:07.01 P5468 T6068 Warning NHibernate ] Oracle.DataAccess.Client.OracleException ORA-00900: invalid SQL statement bij Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
bij Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
bij Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
bij Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
bij System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
bij NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
bij NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
[2007-07-19 13:15:07.01 P5468 T6068 Error NHibernate ] ORA-00900: invalid SQL statement
[2007-07-19 13:15:07.03 P5468 T6068 Debug NHibernate ] Closed IDbCommand, open IDbCommands: 0
[2007-07-19 13:15:07.03 P5468 T6068 Debug NHibernate ] aggressively releasing database connection
[2007-07-19 13:15:07.03 P5468 T6068 Debug NHibernate ] Closing connection
[2007-07-19 13:15:07.05 P5468 T6068 Debug NHibernate ] could not execute query
[ exec dtn_adapter_ppe.test_ref ]

[2007-07-19 13:15:07.05 P5468 T6068 Warning NHibernate ] Oracle.DataAccess.Client.OracleException ORA-00900: invalid SQL statement bij Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
bij Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
bij Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
bij Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
bij System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
bij NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
bij NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
bij NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
bij NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
bij NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
[2007-07-19 13:15:07.05 P5468 T6068 Error NHibernate ] ORA-00900: invalid SQL statement
[2007-07-19 13:15:07.51 P5468 T6068 Debug NHibernate ] after autocommit
[2007-07-19 13:15:07.51 P5468 T6068 Debug NHibernate ] aggressively releasing database connection
[2007-07-19 13:15:07.51 P5468 T6068 Debug NHibernate ] transaction completion
Top
 Profile  
 
didier.kuttel
 Post subject:
PostPosted: Fri Aug 24, 2007 11:48 am 
Newbie

Joined: Mon Aug 13, 2007 11:51 am
Posts: 10
Hello,

Same problem ... No way to execute function (nor stored procedures) on Oracle ... (respecting the documentation)

Gives it any planing to fix this bug or to implement full support of stored procedures for Oracle ?

Is anybody executing func/stored proc against Oracle successfully ? (please with sample mapping and store proc signature)

Thanks for any info
Bye
Didier
Top
 Profile  
 
bonanza
 Post subject: using oracle stored procedures from hibernate
PostPosted: Sat Nov 01, 2008 6:09 am 
Newbie

Joined: Sat Nov 01, 2008 6:00 am
Posts: 1
hi, it is a magic knowledge :)

try to set in your database connection configuration file option
<property name="use_sql_comments">false</property>
Top
 Profile  
 
masong
 Post subject: Re: Oracle stored procedure with ref cursor still fails
PostPosted: Thu May 14, 2009 12:15 pm 
Newbie

Joined: Thu May 14, 2009 7:48 am
Posts: 2
I have tried all of the suggestions with both procedures and functions, but can't get them to work at all.
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
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » NHibernate 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.