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/nhibernate/src/NHibernate.Test/SqlTest/Custom/Oracle/Mappings.hbm.xml?view=markup
http://nhibernate.svn.sourceforge.net/viewvc/nhibernate/trunk/nhibernate/src/NHibernate.Test/SqlTest/Custom/Oracle/StoredProcedures.hbm.xml?view=markup
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.
