Calling Oracle stored procedure that returns Ref Cursor

[dmitriys]

Can anyone comment if above is supported by NHibernate? I searched
around and looks like it is still an open issue
http://jira.nhibernate.org/browse/NH-847
http://forum.hibernate.org/viewtopic.php?t=968269
Are there any workarounds?

Thanks,
Dmitriy

[Fabio Maulo]

At some moment I must install ORACLE in my PC.

The NH-Oracle community not appear so active....
I write the SchemaUpdate for Oracle but not hear any comment about it...
We have NHibernate.Test.SqlTest available for MsSQL and FireBird but not other RDBMS user was interested to send tests and eventually patch...

If the NH-ORACLE community don't help us there is no other option than wait that some NH developer install ORACLE, study it, study each failing test, write new specific test, and fix any kind of problem.
--
Fabio Maulo

P.S. remember that NH developers don't have sponsors

2008/7/25 dmitriys <dshva...@gmail.com>

[dmitriys]

Fabio,

I may take a look at this issue since stored proc support is an
importance part in "selling" NHibernate on my current project.
What would be the best place to start?

Thanks,
Dmitriy

On Jul 26, 12:32 am, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> At some moment I must install ORACLE in my PC.
>
> The NH-Oracle community not appear so active....
> I write the SchemaUpdate for Oracle but not hear any comment about it...
> We have NHibernate.Test.SqlTest available for MsSQL and FireBird but not
> other RDBMS user was interested to send tests and eventually patch...
>
> If the NH-ORACLE community don't help us there is no other option than wait
> that some NH developer install ORACLE, study it, study each failing test,
> write new specific test, and fix any kind of problem.
> --
> Fabio Maulo
>
> P.S. remember that NH developers don't have sponsors
>

> 2008/7/25 dmitriys <dshvads...@gmail.com>

[Fabio Maulo]

2008/7/28 dmitriys <dshva...@gmail.com>

What would be the best place to start?

- Download NH sources.
- Check each failing test and each ignored test.
- For ignored tests check if there is a way to implement it for Oracle.
- create JIRA and patches where needed

I know that is not so easy but necessary.

--
Fabio Maulo

[Ricardo Borges]

"Getting Started with the NHibernate Source Code"

 

http://www.hibernate.org/428.html

2008/7/30 Fabio Maulo <fabio...@gmail.com>

[dmitriys]

Done all that (well most of it ;-) )
The issue I run into so far is that Oracle (I used 10g Express edition
for my tests) does not support
{ ? = call selectAllEmployments() }
syntax for function call. According to both Hibernate and NHibernate
documentation that's the only syntax supported
http://www.hibernate.org/hib_docs/reference/en/html/querysql.html
http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querysql.html#sp_query

I'm by no means an Oracle expert. Can any Oracle guru on the list help
out with that?

Thanks,
Dmitriy

On Jul 30, 1:48 pm, "Ricardo Borges" <ricardobor...@gmail.com> wrote:
> "Getting Started with the NHibernate Source Code"
>
> http://www.hibernate.org/428.html
>

> 2008/7/30 Fabio Maulo <fabioma...@gmail.com>
>
> > 2008/7/28 dmitriys <dshvads...@gmail.com>

[Brendan]

Dmitriy,

I’m not an oracle expert either but, don’t bother with the examples
for NHibernate executing oracle stored procedures. They just don’t
work. In fact, they probably need to be reviewed / removed.

As to your original question:

>stored proc support is an importance part in "selling" NHibernate on my current project.

=If most of your project is driven through stored procedures then
NHibernate is NOT the tool for you. If your stored procs contain most
of the logic around concurrency control, validation etc, then you’re
circumventing NHibernate’s magic anyway. You’re probably better off
just implementing your own datalayer with ADO.NET.

Back to the issue of Oracle Stored Proc support, I believe that the
issue is a little more complicated. Mainly centred on the following
points:
1. There is no way to add an OracleRefCursor as an OUT parameter.
(Also revolves around being bound to the generic DbType)
2. There is no way of then feeding that ref cursor into the method
that Hydrates the requested objects.
3. Need some way of doing this through a named sql query to support
the return column mapping.

The oracle support in NHibernate as suggested above is not perfect. If
you’re simply using NHibernate as an ORM, then I’ve found that the
oracle support works ok without too many issues. I added a ticket
recently to try and get some of the changes we made back to the
dialect (UInt Support http://jira.nhibernate.org/browse/NH-1382)

To get Stored procedure support working, these are my thoughts as a
starting point:
In NHibernate 1.2: Inheriting the ‘OracleDataClientDriver’ means you
would be able to override the CreateBatcher class. Then inherit the
Batcher to override functionality around the ExecuteReader()…
In NHibernate 2.0: Looks as though things have been moved around a
little, but now ‘OracleDataClientDriver’ looks to have methods called
‘SetCommandParameters’, ‘GenerateParameter’ and
‘GenerateOutputParameter’. Might be worth looking into.

I do believe that not using Stored procs in Oracle on occasions is
simply a waste, just because they are a powerful way of leveraging the
DB. Having that support would be really nice to have, but, it seems to
be an ongoing issue with no clear solution, so don’t hold your breath.

@Fabio: I think the UpdateSchema functionality is a great idea,
however, just the corporate nature that most Oracle DBs exist in means
there are scary DBAs who don’t want any ‘magical’ updating happening
to their happy little databases.
@Scary DBAs: Would it be ok to use if NHibernate emails you the sql
before it gets executed…as part of the transaction :p

On Jul 31, 6:37 am, dmitriys <dshvads...@gmail.com> wrote:
> Done all that (well most of it ;-) )

> The issue I run into so far is thatOracle(I used 10g Express edition

> for my tests) does not support
> { ? = call selectAllEmployments() }
> syntax for function call. According to both Hibernate and NHibernate

> documentation that's the only syntax supportedhttp://www.hibernate.org/hib_docs/reference/en/html/querysql.htmlhttp://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/qu...
>
> I'm by no means anOracleexpert. Can anyOracleguru on the list help

[Ayende Rahien]

On Fri, Aug 1, 2008 at 4:06 AM, Brendan <brendan...@gmail.com> wrote:

As to your original question:
>stored proc support is an importance part in "selling" NHibernate on my current project.
 =If most of your project is driven through stored procedures then
NHibernate is NOT the tool for you. If your stored procs contain most
of the logic around concurrency control, validation etc, then you're
circumventing NHibernate's magic anyway. You're probably better off
just implementing your own datalayer with ADO.NET.

Actually, I would suggest iBatis.NET for this scenario.

I do believe that not using Stored procs in Oracle on occasions is
simply a waste, just because they are a powerful way of leveraging the
DB. Having that support would be really nice to have, but, it seems to
be an ongoing issue with no clear solution, so don't hold your breath.

I agree. But you need to separate what you are using SP for (tends to be set based actions) and what you tend to use ORM for (domain logic)
 

@Fabio: I think the UpdateSchema functionality is a great idea,
however, just the corporate nature that most Oracle DBs exist in means
there are scary DBAs who don't want any 'magical' updating happening
to their happy little databases.

I would NEVER do an UpdateSchema on a production DB without first going _very carefully_ over the generated script.

@Scary DBAs: Would it be ok to use if NHibernate emails you the sql
before it gets executed…as part of the transaction :p

Can I leave the transaction open and lock the entire DB as well?
Does ORA have transactional DDL, for that matter?

[Brendan Kowitz]

>Can I leave the transaction open and lock the entire DB as well?

haha sure ;) if thats what makes dbas happy.

On Aug 1, 11:22 am, "Ayende Rahien" <aye...@ayende.com> wrote:

[dmitriys]

Brendan,
Thanks a lot for pointing me in the right direction. I was able to get
Oracle stored procs working with some limitations.
Here is the code if anyone is interested. I know it's an ugly hack
(stored procs have to have a particular prefix name and REF CURSOR has
to be the last parameter) but it works.

public class OracleDataClientDriverWithSP : OracleDataClientDriver
{
public override IDbCommand GenerateCommand(CommandType type,
SqlString sqlString, SqlType[] parameterTypes)
{
IDbCommand cmd = base.GenerateCommand(type, sqlString,
parameterTypes);
if(sqlString.StartsWithCaseInsensitive("PROC"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ExtractStoredProcName(sqlString);
((OracleCommand) cmd).BindByName = false;
foreach (IDbDataParameter param in cmd.Parameters)
{
param.ParameterName = "";
}
((OracleCommand)cmd).Parameters.Add(new
OracleParameter("", OracleDbType.RefCursor,
ParameterDirection.Output));
}
return cmd;
}

private string ExtractStoredProcName(SqlString sqlCommandString)
{
int indexOfParamClouse =
sqlCommandString.IndexOfCaseInsensitive("(");
if(indexOfParamClouse > 0)
{
return
sqlCommandString.ToString().Remove(indexOfParamClouse);
}
return sqlCommandString.ToString();
}
and the mapping is

<sql-query name="selectAllEmploymentsForEmployee" callable="true">
<return class="Employment">
<return-property name="Employee" column="EMPLOYEE"/>
<return-property name="Employer" column="EMPLOYER"/>
<return-property name="StartDate" column="STARTDATE"/>
<return-property name="EndDate" column="ENDDATE"/>
<return-property name="RegionCode" column="REGIONCODE"/>
<return-property name="id" column="EMPID"/>
<return-property name="Salary">
<!-- as multi column properties are not supported via the
{}-syntax, we need to provide an explicit column list for salary
via <return-property> -->
<return-column name="VALUE"/>
<return-column name="CURRENCY"/>
</return-property>
</return>
PROC_selectAllEmploymentsFE(:EMPNAME)
</sql-query>

@ Ayende: Yes I understand the limitations once we start using stored
procs with NHIbenate. The idea is to offload few complex queries and
some cross system/cross database queries to stored procedures and
utilize NHibernate goodness (lazy load, change tracking, etc.) for the
rest of the application.

Dmitriy

On Jul 31, 9:06 pm, Brendan <brendan.kow...@gmail.com> wrote:
> Dmitriy,
>
> I’m not an oracle expert either but, don’t bother with the examples
> for NHibernate executing oracle stored procedures. They just don’t
> work. In fact, they probably need to be reviewed / removed.
>
> As to your original question:>stored proc support is an importance part in "selling" NHibernate on my current project.
>
>  =If most of your project is driven through stored procedures then
> NHibernate is NOT the tool for you. If your stored procs contain most
> of the logic around concurrency control, validation etc, then you’re
> circumventing NHibernate’s magic anyway. You’re probably better off
> just implementing your own datalayer with ADO.NET.
>
> Back to the issue of Oracle Stored Proc support, I believe that the
> issue is a little more complicated. Mainly centred on the following
> points:
> 1.      There is no way to add an OracleRefCursor as an OUT parameter.
> (Also revolves around being bound to the generic DbType)
> 2.      There is no way of then feeding that ref cursor into the method
> that Hydrates the requested objects.
> 3.      Need some way of doing this through a named sql query to support
> the return column mapping.
>
> The oracle support in NHibernate as suggested above is not perfect. If
> you’re simply using NHibernate as an ORM, then I’ve found that the
> oracle support works ok without too many issues. I added a ticket
> recently to try and get some of the changes we made back to the

> dialect (UInt Supporthttp://jira.nhibernate.org/browse/NH-1382)

> > documentation that's the only syntax supportedhttp://www.hibernate.org/hib_docs/reference/en/html/querysql.htmlhttp......

[Brendan Kowitz]

Very impressive!
...so the limitations you are talking about are that ALL parameters
must be specified in the correct order with the Ref Cursor being the
last. I think even having any ability to execute the procs is better
then none.

Could this relate to the ticket: http://jira.nhibernate.org/browse/NH-847?

Also, for this stuff to be considered into nhibernate we need to
remove the dependency on Oracle.DataAccess, I've made a few changes
below, and also the 'convention' to match the SqlServer 'exec' style
syntax:

<sql-query name="fetchBrand">
<return class="Brand">
<return-property name="ID" column="BrandId"/>
<return-property name="Name" column="Name"/>
<return-property name="ShortName" column="ShortName"/>
<return-property name="DisplayOrder" column="DisplayOrder"/>
<return-property name="TimeStamp" column="rowstamp"/>
</return>
exec BRAND_PKG.FetchBrand(:pBrandId)
</sql-query>

OracleDataClientDriver:

public override IDbCommand GenerateCommand(CommandType type,

NHibernate.SqlCommand.SqlString sqlString,
NHibernate.SqlTypes.SqlType[] parameterTypes)

{
IDbCommand cmd = base.GenerateCommand(type, sqlString,
parameterTypes);

if (sqlString.StartsWithCaseInsensitive("exec"))

{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ExtractStoredProcName(sqlString);

AddOracleRefCursor(cmd);
}
return cmd;
}

private void AddOracleRefCursor(IDbCommand cmd)
{
//Set BindByName to false
PropertyInfo bindByNameInfo =
cmd.GetType().GetProperty("BindByName");
bindByNameInfo.SetValue(cmd, false, null);

//Create a RefCursor as an out parameter
IDbDataParameter refCurParam = cmd.CreateParameter();
PropertyInfo oracleDbTypeInfo =
refCurParam.GetType().GetProperty("OracleDbType");
object refCursor = Enum.Parse(oracleDbTypeInfo.PropertyType,
"RefCursor");
oracleDbTypeInfo.SetValue(refCurParam, refCursor, null);
refCurParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(refCurParam);
}

private string ExtractStoredProcName(NHibernate.SqlCommand.SqlString
sqlCommandString)
{
string retval = sqlCommandString.ToString();

int indexOfParamClause =
sqlCommandString.IndexOfCaseInsensitive("(");
if (indexOfParamClause > 0)
{
retval = retval.Remove(indexOfParamClause);
}
int indexOfExec = retval.IndexOf(" ");
if(indexOfExec > 0)
{
retval = retval.Remove(0, indexOfExec);
}

return retval;
}

Great work Dmitriy.

[dmitriys]

Great corrections Brendan. It does make code a bit more generic and
not dependent directly on Oracle.DataAccess.
As for the ticket http://jira.nhibernate.org/browse/NH-847 I think the
issue is more general and this code feels more like a work around for
the specific problem rather then general solution. While looking
through NHibernate code I saw some reference to better support of
stored procs in future versions (support for "callable" attribute in
the Loader for example). I hope to see more of the stored proc support
implemented in the NHibernate core rather then hacking the driver by
changing the CommandType and adding parameters on the fly. In the
meantime I'm happy to have a working solution even if it's not
perfect. Thanks a lot for you help.

Dmitriy

[Fabio Maulo]

2008/8/5 dmitriys <dshva...@gmail.com>

..... While looking

through NHibernate code I saw some reference to better support of
stored procs in future versions (support for "callable" attribute in

the Loader for example).....

Yes, is one target for NH2.1.0.
--
Fabio Maulo