Thanks a lot Richard, On Nhibernate Tests i was looking into examples on my local code base which seem to have been out of sync and did not have the example for selectEmploymentsForRegion. Thanks for pointing that out.
When i initially tried with OracleDataClient, it failed with a cast error, a quick search revealed that we need to set hbm2ddl.keywords property to none in session factory config to do away with the error. Stored Procedures are working fine now, however i still need to think through this as the current project for which i am considering already uses MS Oracle Client, and this approach requires OracleDataClient. I decided to look at the NHibernate Sources to see whether i can do something on adding support, while i made some progress by extending OracleClientDriver, i hit some road blocks for other reasons, one was that Microsoft's OracleClient provider only supports parameters binding by name and not position, and the other one was that NHibernate seems to regenerate parameter names and not use the ones provided in the Mapping file. I noticed that NH-847 was fixed by you, was there any other reason that you could not support MS's OracleClient Regards Dinesh On Jan 21, 5:52 am, "Richard Brown (gmail)" <[email protected]> wrote: > Hi Dinesh, > > There are a couple of examples using stored-procedures at the end of the > tests: > > http://nhibernate.svn.sourceforge.net/viewvc/nhibernate/trunk/nhibern... > > http://nhibernate.svn.sourceforge.net/viewvc/nhibernate/trunk/nhibern... > > The stored proc you have looks fine. Also, verify that you're using the > OracleDataClient (ODP.Net) driver. > > If I remember correctly I think you need to remove the 'callable' attribute, > and use curly braces round the function call. I think the mapping would be: > > <sql-query name="FetchUsers"> > <return alias="Users" class="User"> > </return> > { call astmGetAllUsers_prc } > </sql-query> > </hibernate-mapping> > > Let me know how you get on. > > Cheers, > Richard > > -------------------------------------------------- > From: "Dinesh" <[email protected]> > Sent: Wednesday, January 20, 2010 6:24 PM > To: "nhusers" <[email protected]> > Subject: [nhusers] Support for loading entities from Oracle Stored Procedure > > > > > I am trying to load Entities from an Oracle Stored Procedure (not > > function), as per NH-847 Stored Procedures with ref cursor out should > > be supported. i am using v2.1.2, so this fix should be available. > > However i am having problems in calling the stored procedure. as per > > the documentation the first parameter of the procedure should be out > > sys_refcursor and my procedure satisfies this condition. Below is a > > simple example of the scenario: > > > Procedure: > > CREATE OR REPLACE PROCEDURE GetAllUsers( > > cp_users_out OUT sys_refcursor > > ) > > AS > > BEGIN > > OPEN cp_users_out FOR > > Select User_ID,User_name From Users; > > END; > > > Mapping File: > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > > namespace="NHibSample.Entities" assembly="NHibSample"> > > > <class name="User"> > > <id name="Id" column="User_Id"/> > > <property name="Name" column="User_Name"/> > > </class> > > > <sql-query name="FetchUsers" callable="true"> > > <return alias="Users" class="User"> > > </return> > > call astmGetAllUsers_prc() > > </sql-query> > > </hibernate-mapping> > > > Code: > > var usersList = session.GetNamedQuery("FetchUsers").List(); > > > Error Message: > > ORA-06553: PLS-306: wrong number or types of arguments in call to > > 'ASTMGETALLUSERS_PRC'\n > > > There are some examples for functions but none for Oracle Stored > > procedures, so even though the first parameter over here is sys ref > > cursor, the documentation does not suggest whether the procedure > > should include any paramaters. i tried doing but that failed, saying > > the parameter value is not being set. > > > If anybody has tried out a Oracle Stored Procedure (not function) > > could you please point me to a working example. I looked at Nhibernate > > tests, and they are for functions. > > > Regards > > Dinesh > > > -- > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en.
-- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
