Hello,

I'm not having much luck in getting NHibernate to query a stored procedure in Oracle 9i and return me the results. Searched through the forums but there doesn't seem to be too many working solutions. In fact, I'm banging my head against the wall here and my faith in NHibernate is rapidly eroding.

The error I get is:

===============================
could not execute query
[ { CALL SP_VOLUMEIMPORT(?, ?, ?, ?) } ]
Name: p_grp_code - Value: REVTYPE
Name: p_to_date - Value: 2008-02-10
Name: p_from_date - Value: 2007-02-10
Name: p_volumes - Value: System.Collections.ArrayList
[SQL: { CALL SP_VOLUMEIMPORT(?, ?, ?, ?) }]

with an inner exception of ORA-00911: invalid character.
===============================

The details for my set-up are as follows:

NHibernate version: 1.2.1.4000

Oracle version: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

Mapping document:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property">
<sql-query name="SP_VOLUMEIMPORT">
<return-scalar column="GROUP_CODE" type="String" />
<return-scalar column="ACTVY_DATE" type="Date" />
<return-scalar column="ACCT_DATE" type="Date" />
<return-scalar column="VOLUME" type="Double" />
<return-scalar column="UNITS" type="Double" />
<return-scalar column="ENTITY_CODE" type="String" />
<return-scalar column="LEVEL_CODE" type="String" />
<return-scalar column="PARENT_ENTITY_CODE" type="String" />
<return-scalar column="PARENT_LEVEL_CODE" type="String" />
<return-scalar column="PROD_CODE" type="String" />
<return-scalar column="HIERARCHY_CODE" type="String" />
{ CALL SP_VOLUMEIMPORT(:p_volumes, :p_from_date, :p_to_date, :p_grp_code) }
</sql-query>
</hibernate-mapping>

.NET Code, i.e. method body for making the query:

string groupCode = "REVTYPE"

IQuery query = session.GetNamedQuery("SP_VOLUMEIMPORT");
query.SetParameter("p_volumes", new ArrayList());
query.SetParameter("p_from_date", dateRange.LowBound.ToString("yyyy-MM-dd"));
query.SetParameter("p_to_date", dateRange.HighBound.ToString("yyyy-MM-dd"));
query.SetParameter("p_grp_code", groupCode);
//query.SetResultTransformer(Transformers.AliasToBean(typeof(IVolumeDataRow)));
return MapTo(query.List());

Do I even set a parameter for output parameter for the stored procedure and if so which is the value object it is expecting?

The input parameter dateRange is simply a pair of dates (.NET DateTime), lowbound and highbound. I'm expecting the method to return an IList<IIVolumeDataRow>. The MapFrom method should map an IList of objects to IList<IIVolumeDataRow>. Once I get any resultset that is.

IIVolumeDataRow is a simple object that mirrors excatly the return-scalar properties shown in the mapping file. The stored procedure joins data from five data tables and is essentially:

create or replace PROCEDURE sp_VolumeImport
(
p_volumes OUT SYS_REFCURSOR,
p_from_date DATE,
p_to_date DATE,
p__grp_code VARCHAR2
)
AS

BEGIN
OPEN p_volumes FOR
SELECT...
FROM...
WHERE...(including join clauses)
GROUP BY...

END;

I'd be gratefully of any pointers to getting this running and will certainly share anything I find in getting this working.

Many thanks,

Gavin

Just to add, the interface for the return class is

public interface IVolumeDataRow
{
string GroupCode { get; set; }
DateTime ActivityDate { get; set; }
DateTime AccountingDate { get; set; }
double Volume { get; set; }
double Units { get; set; }
string EntityCode { get; set; }
string LevelCode { get; set; }
string ParentEntityCode { get; set; }
string ParentLevelCode { get; set; }
string ProductCode { get; set; }
string HierarchyCode { get; set; }
}

Again thanks.

Looks as if the braces ({ and }) around the SQL call on the stored procedure caused the invalid character error. Removing them now results in the following error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'SP_VOLUMEIMPORT'

Looks as if mapping an ArrayList() to a SYS_REFCURSOR is a potential issue. What should the output parameter type be?

In the end gave up and got the data we required using the SQL statement in a named query. Expediency won. Wouldn't want to have to make calls to legacy stored procedures. Here's hoping NHibernate 2.x gives us a better handle on this kind of strategy, even if it's just improved error notification of what's happening under the hood.

Thanks for listening;-)

Just to update. It looks as if it's not supported in 1.x, but may now make 2.x.

See http://groups.google.com/group/nhusers/ ... 7ef496966#