Stored procedures problems with Oracle / NHibernate 1.2.0

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

I have some problems using stored procedures with NHibernate.
CRUD operations are OK but it seems impossible tu use stored procedures to define our own queries.

I work with the last version of NHibernate (NHibernate 1.2.0 beta 2) and with Oracle 10g.

My config file contains:

Code:

...
<property name="dialect">NHibernate.Dialect.OracleDialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
<property name="connection.connection_string">
....

Following the documentation I tried some basic manipulations without succeeding everything.
Getting data from a basic sql call (filling scalar or objects) works but it doesn't work if I just want to fill 2 members of an object (it's the SelectALLSQLPART query).

For each time I tried to call procedure in a query it didn't work.

Is it a known bug for Oracle or my mapping is not correct?

mapping file:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="MappingNHibernate" assembly="MappingNHibernate">

   <class name="Accounts" table="ACCOUNTS">
      <id name="Id" type="Int32" unsaved-value="0">
         <column name="ACCOUNTID" sql-type="NUMBER" not-null="true" unique="true" index="IDX_ACCOUNTS"/>
         <generator class="sequence">
            <param name="sequence">ACCOUNTID_SEQ</param>
         </generator>
      </id>
      <property name="Login" type="String">
         <column name="LOGIN" length="128" sql-type="NCHAR" not-null="true" unique="true" index="SYS_C005624"/>
      </property>
      <property name="Password" type="String">
         <column name="PASSWORD" length="128" sql-type="NCHAR" not-null="true"/>
      </property>
      <property name="Activated" type="String">
         <column name="ACTIVATED" length="1" sql-type="CHAR" not-null="true" index="IDX_ACCOUNTS"/>
      </property>
   </class>

   <sql-query name="SelectACCOUNTS">
      <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
        Call  ACCOUNTS_P.GetACCOUNTS( ?)
     </sql-query>

  <sql-query name="SelectALLF">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
    Call ACCOUNTS_P.GetAllAccountsF
  </sql-query>

  <sql-query name="SelectALLSP">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
    Call ACCOUNTS_P.GetAllAccountsSP()
  </sql-query>

  <sql-query name="SelectALLSQLFULL">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate"/>
    SELECT * FROM ACCOUNTS
  </sql-query>

  <sql-query name="SelectALLSQLPART">
    <return alias="acc" class="MappingNHibernate.Accounts, MappingNHibernate">
      <return-property name="Login" column="myLogin"/>
    </return>
    SELECT Login myLogin FROM ACCOUNTS
  </sql-query>

  <sql-query name="SelectALLSQLScalar">
    <return-scalar column="ACCOUNTID" type="Int32"/>
    <return-scalar column="LOGIN" type="String"/>
    SELECT ACCOUNTID, LOGIN FROM ACCOUNTS
  </sql-query>
  
<sql-query name="SelectALLSPScalar">
    <return-scalar column="test" type="String"/>
    Call ACCOUNTS_P.GetAllAccountsSP()
  </sql-query>
</hibernate-mapping>

mapping class:

Code:

using System;
using System.Collections;
using System.Web.UI.WebControls;

namespace MappingNHibernate
{
   #region Accounts

   /// <summary>
   /// Accounts object for NHibernate mapped table 'Accounts'.
   /// </summary>
   public class Accounts
      {
      #region Member Variables
      
      protected int _id;
      protected string _login;
      protected string _password;
      protected string _activated;
                     
      #endregion

      #region Constructors

      public Accounts() { }

      public Accounts( string login, string password, string activated )
      {
         this._login = login;
         this._password = password;
         this._activated = activated;
      }

      #endregion

      #region Public Properties

      public virtual int Id
      {
         get {return _id;}
         set {_id = value;}
      }

      public virtual string Login
      {
         get { return _login; }
         set
         {
            if ( value != null && value.Length > 128)
               throw new ArgumentOutOfRangeException("Invalid value for Login", value, value.ToString());
            _login = value;
         }
      }

      public virtual string Password
      {
         get { return _password; }
         set
         {
            if ( value != null && value.Length > 128)
               throw new ArgumentOutOfRangeException("Invalid value for Password", value, value.ToString());
            _password = value;
         }
      }

      public virtual string Activated
      {
         get { return _activated; }
         set
         {
            if ( value != null && value.Length > 1)
               throw new ArgumentOutOfRangeException("Invalid value for Activated", value, value.ToString());
            _activated = value;
         }
      }

      #endregion
      
      
   }

   #endregion
}

sample test code:

Code:

IQuery q = session.GetNamedQuery("SelectALLSP");
IList accounts = q.List();

Code:

I get these errors when i call procedure and function:
{"ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETALLACCOUNTSSP'"}
and
{"ORA-06576: not a valid function or procedure name"}

I did the test with a sample ADO.NET code and my stored procedure and function work perfectly.
The call made by NHibernate seems to be the problem...

If anyone has information about that...

Thanks

Nicolas

lvildosola · **Posted:** Thu Dec 07, 2006 5:22 am

Can you try setting the dialect to Oracle9Dialect instead of OracleDialect like the following snippet?

Code:

<property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>

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

I changed the dialect but it still doesn't work.

I think the problem is the way I use to get the results of an oracle function or the results of an out parameter of my stored procedure.

Here are the queries with the problems:

Code:

<sql-query name="SelectALLF">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
    Call ACCOUNTS_P.GetAllAccountsF
  </sql-query>

  <sql-query name="SelectALLSP">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
    Call ACCOUNTS_P.GetAllAccountsSP()
  </sql-query> 

Oracle function and procedure:

Code:

  TYPE T_CURSOR IS REF CURSOR;

  FUNCTION GetAccountsF RETURN T_CURSOR
  IS
    cur_ACCOUNTS T_CURSOR;
  BEGIN
      OPEN cur_ACCOUNTS FOR
       SELECT * FROM ACCOUNTS;
  END GetAccountsF;

  PROCEDURE GetAllAccountsSP (cur_ACCOUNTS OUT T_CURSOR)
  IS
  BEGIN
  OPEN cur_ACCOUNTS FOR
       SELECT * FROM ACCOUNTS;
   END GetAllAccountsSP;

Thanks

Nicolas

lvildosola · **Posted:** Thu Dec 07, 2006 6:13 am

This is just another idea, I have made 2 changes to your named query. I added the attribute callable="true" and I changed the format of your call definition. All examples I see are using that format. I don't have access to my Oracle instance to try it for you. So give it a shot.

Code:

<sql-query name="SelectALLSP" callable="true"> 
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" /> 
    { ? = call ACCOUNTS_P.GetAllAccountsSP() }
</sql-query>

Refer to the Hibernate documentation for details and an example.

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

The callable attribute is not an attribute for NHibernate mapping, it raises an XmlSchemaException when I use it. I think it's one of the mapping difference between Hibernate and NHibernate.

I used the format you gave me to call my procedure and my function:

Code:

  <sql-query name="SelectALLF">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
    { ? = Call ACCOUNTS_P.GetAllAccountsF() }
  </sql-query>

  <sql-query name="SelectALLSP">
    <return alias="ACCOUNTS" class="MappingNHibernate.Accounts, MappingNHibernate" />
    { Call ACCOUNTS_P.GetAllAccountsSP(?) }
  </sql-query>

I obtained the following error:
Not all positional parameters have been set. Expected 1, set 0 [{ ? = Call ACCOUNTS_P.GetAllAccountsF() }]

Do I need to set a parameter in my instance of NHibernate.IQuery? => I tried but it didn't work...

I'm a little bit lost because I'm strictly following samples of NHibernate documentation (Chapter 13. Native SQL -> 13.2. Named SQL queries -> 13.2.1. Using return-property to explicitly specify column/alias names
-> 13.2.2. Using stored procedures for querying)

Thanks

Nicolas

lvildosola · **Posted:** Thu Dec 07, 2006 7:32 am

Maybe you are missing the RETURN in your function?

Code:

TYPE T_CURSOR IS REF CURSOR; 

FUNCTION GetAccountsF
    RETURN T_CURSOR
AS 
    cur_ACCOUNTS T_CURSOR; 
BEGIN 
    OPEN cur_ACCOUNTS FOR 
    SELECT * FROM ACCOUNTS; 

    RETURN cur_ACCOUNTS;

END GetAccountsF; 

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

That's true, the RETURN statement was missing.

But it doesn't change the fact that the function is not even called...

It would be perfect if somebody had a sample of NHibernate code / mapping / procedure working with Oracle 10g.

Thanks

Nicolas

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

Hi,

I found 4 topics with the same problem:
http://forum.hibernate.org/viewtopic.php?t=968248
http://forum.hibernate.org/viewtopic.php?t=966733
http://forum.hibernate.org/viewtopic.php?t=967979
http://forum.hibernate.org/viewtopic.php?t=967276

I think we can guess it's a bug...

Somebody knows if it has been reported to be fixed?

Nicolas

bchoi06 · **Joined:** Wed Aug 16, 2006 8:02 pm **Posts:** 11

Anybody can reopen NH-258?

SYS_REFCURSOR is not working

rpn68 · **Joined:** Wed Dec 20, 2006 6:58 pm **Posts:** 1

SYS_REFCURSOR is not working for me either.

Oracle 10g. Tried both function returning SYS_REFCURSOR called from Hibernate named query with ? = call foo(...) approach, and with a PROCEDURE using the first param as an OUT ref cursor.

I also tried declaring a custom REF CURSOR and returning that, to no avail.

As a work around, I am forced to direct my FUNCTION to an ON COMMIT DELETE temp table, and then query that table in the same transaction/session context. Clearly, not an optimal solution.

sergey · **Posted:** Thu Dec 21, 2006 2:46 am

Please submit a bug report.

bchoi06 · **Joined:** Wed Aug 16, 2006 8:02 pm **Posts:** 11

NH-847 is created

bchoi06 · **Joined:** Wed Aug 16, 2006 8:02 pm **Posts:** 11

If you want to this feature implemented please vote this issue.

ado.net using oracle client is already supporting ref cursor type.
We just need to use "callable" attribute in xml mapping (like in java)
to indicate stored proc will be called.

Also
1. need to pass this parameter somehow (for example for load)
2. map its result to back to object (I believe this should be the way without any changes).

Stored procedure, even though I prefer plain sql + hibernate,
is a must for any enterprise with established policy (specially many DB admins ^^). Transparent mapping source of hibernate (either from sql, stored proc, xml, text and so on) will definitely broaden user bases and usage scenarios.

bchoi06 · **Joined:** Wed Aug 16, 2006 8:02 pm **Posts:** 11

http://jira.nhibernate.org/browse/NH-847

thinkrama · **Joined:** Thu Mar 15, 2007 10:34 am **Posts:** 8

bchoi06 wrote:

http://jira.nhibernate.org/browse/NH-847