How to call Oracle stored procedure from NHibernate?"

Raul · **Posted:** Wed Dec 06, 2006 9:21 am

Hi,

How to call Oracle stored procedure from NHibernate?"

Regards,

Raul

nikob · **Joined:** Wed Dec 06, 2006 12:58 pm **Posts:** 6

Hi,

I have the same problem: http://forum.hibernate.org/viewtopic.php?t=968269.

I really feel alone with that, samples in documentation don't seem to work with Oracle.

Nicolas

abous · **Posted:** Wed Jan 10, 2007 10:25 am

Hi,

I have spent most of the day fiddeling arround with oracle stored procedures. Here are my findings:

> Use 'call' not 'exec' as described in the manual
> enclose the parameters in brackets
> use the 'checks="none"' Attribute if the stored procedure does not return the affected rows count
> the only parameter(s) of your delete sp should be the pk field(s)
> in the update and insert sp, the parameters should be in the order you have defined the cols in you mapping file, exept of the pk field(s) - they should be last
> the order of the xml nodes should be in the right order - insert, update delete. Otherwise you get a XML Validation Error

The actual call executed by NHiberante, with the appropriate parameter values can be found in the log file - please check the file, to make sure that the parameters are correct :-)

Unfortunatly I have not worked out a way of naming the parameters! Maybe somebody can help us on that!

My Example Mapping File:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Data.Entities.CatTStaticdata,CatCommon" table="CAT_T_STATICDATA">


<id name="Id" column="ID" type="Int32" unsaved-value="0">
<generator class="sequence">
<param name="sequence">CAT_T_SEQ</param>
</generator>
</id>
<property column="CATEGORY" type="String" name="Category" not-null="true" length="50" />
<property column="NAME" type="String" name="Name" not-null="true" length="100" />
<property column="VALUE" type="Int32" name="Value" not-null="true" />
<property column="DESCRIPTION" type="String" name="Description" length="250" />

<sql-insert check="none">call MYGEN.INSERT_CAT_T_STATICDATA (?,?,?,?,?)</sql-insert>
<sql-update check="none">call MYGEN.UPDATE_CAT_T_STATICDATA (?,?,?,?,?)</sql-update>
<sql-delete check="none">call MYGEN.DELETE_CAT_T_STATICDATA (?)</sql-delete>

</class>
</hibernate-mapping>

Hope that these findings are helpful

masong · **Joined:** Thu May 14, 2009 7:48 am **Posts:** 2

Hi,

Did you try to use a stored procedure to load from as well? I have been trying to use a "read" stored procedure, but cant find a way to set the out parameter:

The mapping is defined like this:

Code:

  <sql-query name="selectAllActions_SP">
    <return alias="acimActions" class="NHAcimActions">
      <return-property name="actioncode" column="ACIM_ACTION_CODE"/>
      <return-property name="actiondesc" column="ACIM_ACTION_DESCRIPTION"/>
      <return-property name="actionsecs" column="ALLOWABLE_ELAPSE_SECS"/>
    </return>
    call ACIM_ACTIONS_PKG.get_all_acim_actions(?)
  </sql-query>

If I try to use this as a named query

Code:

IList allActions = session.GetNamedQuery("selectAllActions_SP")
                         .List()

I get an error:

Quote:

System.Collections.ListDictionaryInternal
Expected positional parameter count: 1, actual parameters: [] [call ACIM_ACTIONS
_PKG.get_all_acim_actions(?)]

Is there a way to set this?