[nhibernate-issues] [JIRA] Updated: (NH-847) Oracle stored procedure with Ref Cursor out

[NHibernate J. <nh...@gm...>]

     [ http://nhjira.koah.net/browse/NH-847?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Michael Hanney updated NH-847:
------------------------------

    Attachment: SqlTests-Oracle-OracleCustomSQLFixture-10g-TestResult.zip

NUnit Test results attached for Oracle 10g Enterprise Edition 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining option.

1 pass, 3 fail

Config is:
{noformat} 
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory name="NHibernate.Test">    
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
    <property name="connection.connection_string">Data source=MG10GR2;User Id=mhanney;Password=mhanney;</property>
    <property name="show_sql">False</property>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
	<property name="show_sql">true</property>
	<property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
  </session-factory>
</hibernate-configuration>
{noformat} 

Driver is OracleDataClientDriver, (from Oracle, not the MS one)

Oracle.DataAccess.Client.OracleException  ORA-06576: not a valid function or procedure name is misleading -  function names and procedures names are fine and exist (they exist in my db and I can execute them without error from sqlplus).

It is more likely that ORA-06576 is thrown because the return parameter is not bound, and/or not the right type for the ado.net driver - Oracle.DataAccess.Types.OracleRefCursor.

For the following PL/SQL function:

{noformat} 
create or replace FUNCTION allEmployments
RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR;
BEGIN
OPEN st_cursor FOR
SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE,
REGIONCODE, EMPID, VALUE, CURRENCY
FROM EMPLOYMENT;
RETURN st_cursor;
END;
{noformat}

An example in ado.net would be:

{noformat} 
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

class Program
{
    static void Main(string[] args)
    {
        using (OracleConnection con = new OracleConnection("Data Source=MG10GR2; User ID=mhanney; Password=mhanney"))
        {
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = "allEmployments";
            cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter resultCursor = cmd.CreateParameter();
            resultCursor.OracleDbType = OracleDbType.RefCursor;
            resultCursor.Direction = ParameterDirection.ReturnValue;
            resultCursor.ParameterName = "result";
            cmd.Parameters.Add(resultCursor);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                OracleRefCursor refCursor = (OracleRefCursor)cmd.Parameters["result"].Value;
                OracleDataReader reader = refCursor.GetDataReader();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.Write("{0}\t", reader.GetName(i));
                }
                Console.Write("\n");
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Console.Write("{0}\t", reader[i]);
                    }
                    Console.Write("\n");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: {0}", ex);
            }
            con.Close();
        }
    }
}
{noformat} 

I am sorry, I do not have any requirement to use stored procedures with oracle + NH but I can execute tests easily enough in my dev env. I am a somewhat familiar with the NH source, but I would need pointers getting this happening:

            resultCursor.OracleDbType = OracleDbType.RefCursor;
            resultCursor.Direction = ParameterDirection.ReturnValue;

And I do not know how NH is supposed to know that a return parameter needs to be bound. 
Perhaps ? = call allEmployments()

> Oracle stored procedure with Ref Cursor out
> -------------------------------------------
>
>                 Key: NH-847
>                 URL: http://nhjira.koah.net/browse/NH-847
>             Project:  NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta2
>            Reporter: Brian Choi
>            Priority: Minor
>         Attachments: SqlTests-Oracle-OracleCustomSQLFixture-10g-TestResult.zip
>
>
> http://forum.hibernate.org/viewtopic.php?t=968269

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://nhjira.koah.net/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira