DAAB Accessors and OracleDB

Topics: Data Access Application Block
Dec 25, 2010 at 11:54 PM

Hi,
could you please explain, is it possible to use EntLib 5.0 DAAB accessors with Oracle stored procedures (or functions)?
I've tried to use output parameter of SYS_REFCURSOR type in stored procedure (that successfully works in EntLib 4.1), but received Oracle error about "wrong argument count or type".
As far as I understood from reading other discussions, EntLib 5.0 DAAB accessors do not use output parameters.
But how can I return result set from Oracle procedure without output parameters?
Thanks in advance.

Dec 28, 2010 at 12:26 AM
Edited Dec 29, 2010 at 1:11 AM

Accessors can be used in places where you want your stored procedure return rows in business entity form instead of a raw dataset.  And yes, those rows should be what the sp returns and not an output parameter.  Now, I'm not reallly familiar with Oracle, especially ref cursors so I need to clarify this: Does your call use of sys_refcursor in version 4.1 involves a call to ExecuteDataSet and you're converting it to making use of accessor in version 5.0? 

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Dec 28, 2010 at 10:19 AM

Thank you for the responce.

 

I've used following code in version 4.1:

public override List<CharterRecord> SelectCharter(Int64 orgId)
{
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand cmd = db.GetStoredProcCommand("DEPOSITORY.SELECT_CHARTER");
    db.AddInParameter(cmd, "IN_org_id", DbType.Int64, orgId);
    cmd.Parameters.Add(BuildRefCursorParameter("OUT_cur"));
    try
    {
        using (IDataReader reader = db.ExecuteReader(cmd))
            return GetCharterCollFromRdr(reader);
    }
    ....
}
private static OracleParameter BuildRefCursorParameter(String name)
{
    return new OracleParameter(name, OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
}
protected override List<CharterRecord> GetCharterCollFromRdr(IDataReader reader) { var list = new List<CharterRecord>(); while (reader.Read()) list.Add(GetCharterFromRdr(reader)); return list; } protected override CharterRecord GetCharterFromRdr(IDataReader rdr) { return new CharterRecord( (Int64)rdr["id_pk"], ... (Int64)rdr["org_id"]); }

where the Oracle stored package procedure was the following:

PROCEDURE SELECT_CHARTER(IN_org_id NUMBER, OUT_cur OUT SYS_REFCURSOR) IS
BEGIN
    OPEN OUT_cur FOR
        SELECT      C.ID_PK, C.APPROVED_AUTHORITY, ..., C.ORG_ID
        FROM        CHARTER C
        WHERE       C.ORG_ID = IN_org_id
        ORDER BY    C.CREATED;
END;

 

 

In version 5.0 I've tried the following sample:

private static DataAccessor<Customer> _accessor;

class CustomerParameterMapper : IParameterMapper
{
    private readonly Database _db;

    public CustomerParameterMapper(Database db)
    {
        _db = db;
    }
    public void AssignParameters(DbCommand command, object[] parameterValues)
    {
        command.Parameters.Add(BuildRefCursorParameter("OUT_cur"));
    }
    private static OracleParameter BuildRefCursorParameter(String name)
    {
        return new OracleParameter(name, OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
    }
}

public IEnumerable<Customer> FindAllCustomers()
{
    var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();

    _accessor = db.CreateSprocAccessor("CONTEST_REPOSITORY.GET_CUSTOMER",
        new CustomerParameterMapper(db),
        MapBuilder<Customer>.MapAllProperties()
            .Map(p => p.Name).ToColumn("NAME")
            .Build());

    var customerData = _accessor.Execute("dummy").ToList();
    return customerData;
}

where the Oracle stored package procedure is the following:

PROCEDURE GET_CUSTOMER(OUT_cur OUT SYS_REFCURSOR) IS
BEGIN
  OPEN OUT_cur FOR
    SELECT 'ABC' NAME FROM DUAL;
END;

And I get following Oracle error: "wrong number or types of arguments in call to 'GET_CUSTOMER'"

(using Oracle ODP 11.1 data provider).

 

Thanks.

P.S. Please clarify, is EntLib version 5.1 available now (as you refer in your answer)?

Dec 29, 2010 at 1:14 AM

No, sorry that was a typo, the latest version is 5.0. 

We might be missing something basic here, could you try doing it without making use of an accessor first? Converting your 4.1 version to the 5.0 version, no accessors yet, just make use of ExecuteReader and see if it works.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Dec 29, 2010 at 7:38 AM

ExecuteReader in version 5.0 works perfectly with Oracle.DataAccess.Client data provider.

Working sample:

    public class ContestRepository : IContestRepository
    {
        private readonly Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>();

        public IEnumerable<Contest> FindAllContests()
        {
            using (DbCommand cmd = _db.GetStoredProcCommand("CONTEST_REPOSITORY.FIND_ALL_CONTESTS"))
            {
                cmd.Parameters.Add(EnterpiseLibraryOracleExtensions.BuildRefCursorParameter("OUT_cur"));
                try
                {
                    using (IDataReader reader = _db.ExecuteReader(cmd))
                        return GetAllContests(reader);
                }
                catch (Exception ex)
                {
                    ExceptionPolicy.HandleException(ex, "Model Exception Policy");
                    return null;
                }
            }
        }
        private static IEnumerable<Contest> GetAllContests(IDataReader reader)
        {
            var list = new List<Contest>();
            while (reader.Read())
                list.Add(GetContest(reader));
            return list;
        }
        private static Contest GetContest(IDataRecord reader)
        {
            return new Contest
                       {
                           IdPk = reader.GetNullableInt64("ID_PK"),
                           CompanyName = reader.GetNullableString("COMPANY_NAME"),
                           ...
                           CreatedAt = reader.GetNullableDateTime("CREATED_AT")
                       };
        }
    }

And Oracle stored package procedure:
PROCEDURE FIND_ALL_CONTESTS(OUT_cur OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN OUT_cur FOR
        SELECT      ID_PK,
                    COMPANY_NAME,
                    COMPANY_ADDR,
                    ...
                    CREATED_AT
        FROM        CONTEST C
        ORDER BY    C.CONTEST_DATE DESC, ID_PK;
  END;

Dec 29, 2010 at 8:19 AM

Hmm, I don't see anything wrong with the code.  It would be nice if you have tried the ExecuteReader on the same stored procedure you tried to use with EntLib 5.  Anyway, I strongly suggest to debug through the EntLib code specifically in the Execute method.   Internally, it also calls the ExecuteReader and calls the result set mapper after it.

Stepping through the EntLib code should be straightforward since the entlib pdbs are already deployed along with the entlib assemblies.

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Dec 29, 2010 at 2:14 PM
Edited Dec 29, 2010 at 3:36 PM

As far as I understood the debugging results, the reason for the error that the assigned Oracle stored procedure parameter "disappears" after call ToList() function.

For example, I debug following line from my sample code:

var customerData = _accessor.Execute("dummy").ToList();

 

In the Execute method of EntLib's SprocAccessor class, the Oracle cursor parameter is properly assigned:

public override IEnumerable<TResult> Execute(params object[] parameterValues)
{
    using (DbCommand command = Database.GetStoredProcCommand(procedureName))
    {
        parameterMapper.AssignParameters(command, parameterValues);
	// command.Parameters.Count=1
        return base.Execute(command);	
    }
}

But in the Execute method of EntLib's CommandAccessor class, the method argument (DbCommand instance) do not contain previously assigned Oracle stored procedure parameter:

And hence after ExecuteReader call, Oracle complains about "wrong number or types of arguments".

protected IEnumerable<TResult> Execute(DbCommand command)
{	
     // command.Parameters.Count=0
    IDataReader reader = database.ExecuteReader(command);

    foreach (TResult result in resultSetMapper.MapSet(reader))
    {
        yield return result;
    }
}

If you point me to email address, I can send debug screenshots.
P.S. Sorry for my bad English.
Dec 30, 2010 at 9:05 AM

Our email add - entlib.support@avanade.com. Also, if you can be able to send your sample project it can probably help with the investigation too and try to reproduce it in our end (assuming DB provider is not a factor since I'm not that familiar with Oracle as well). Thanks.

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Dec 31, 2010 at 1:20 AM

Do you have any installed Oracle Database 9i/10g/11g for testing purposes?

Without it, my sample project will be useless.

Unfortunately, I can't send you pre-installed, fully configured database instance, because Oracle software do not provide such database transfer.

But if you have one, I'll can send you Oracle script files for deployment to database, along with Visual Studio sample Project.

Thanks.

Jan 3, 2011 at 7:21 AM

I don't see how come the content of its Parameter property got lost when it executes the base method and I don't see this behavior happening on my end.  I didn't use an oracle stored procedure but I don't see how the difference in the database provider should matter in the exact scenario you're describing. 

I was able to install Oracle 11g so I'm interested on your repro.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jan 23, 2011 at 10:15 PM

Sorry for the long delay.

I've sent a test VS2010 project and Oracle sql script for creating tables, procedures, etc.

Aforementioned test project is based on EntLib Hands On Labs DAAB ex04 learning project.

Many thanks.

Feb 1, 2011 at 11:20 AM

Sorry for the late response. Unfortunately it really has been a challenge for us to to try this out in an actual Oracle DB and still not lucky to succesfully try this.

Anyway, I've tried to look for any related thread regarding this and have found this one - http://entlib.codeplex.com/Thread/View.aspx?ThreadId=28268.

Stated from this thread, "in your procedure the cursor parameter name should be "cur_OUT" if you give anyother name it won't work.. you can find this cur_OUT name soemwhere in the EL source code...".

Please try this one and see if this will work for you. Hope this helps.

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 6, 2011 at 2:09 PM

Unfortunately, renaming of parameter does not help.

Meanwhile, ExecuteReader approach works perfectly with any Oracle cursor name.

If I can provide to you remote access to computer with installed Oracle database and VS Express, does it help to investigate the problem?

Feb 7, 2011 at 12:38 AM

Sorry but unfortunately, we are not yet setup to provide support via remote access.  But I would like to ask for the Oracle sql scripts which will create the necessary database objects.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 7, 2011 at 7:17 PM

I have sent you Oracle sql script - please, see scheme.sql file in e-mail attachment.

Feb 8, 2011 at 10:19 AM

Hi,

We're still in progress investigating this. We'll keep you posted for any updates.

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 8, 2011 at 12:16 PM

Thank you for your support. I'll be waiting for your posts.

Feb 9, 2011 at 10:32 AM

Hi,

           We were able to repro the error you're encountering.  Unfortunately, we're not sure yet if the behavior is caused by the ODP.NET provider or if it's a bug itself in the Enterprise Library.  We're still investigating and will let you know once we have any updates.  Sorry for waiting quite long, we haven't worked with Oracle yet before. 

          One thing to note is that I used a different version of Oracle.DataAccess assembly since if we used the one that you're using, the app throws an exception complaining that the provider type isn't compatible with the Oracle version.  I hope this doesn't make much difference.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 9, 2011 at 12:14 PM

Many thanks for this info.

I'm sure that a different version of Oracle.DataAccess assembly does not matter. I'm using last version of Oracle Database and ODP.NET and getting the same error.

Feb 10, 2011 at 10:12 AM

Hi,

Upon further investigation, we haven't found the rootcause of the problem but what we have seen is that it works when the .NET provider (System.Data.OracleClient) for Oracle is used.

I'm afraid we also don't know any other approach how to make it work with ODP.NET provider other than not using Accessors since you have mentioned that it works with ExecuteReader. Which we know it wouldn't give you the same benefit like Accessor does.

Anyway, we hope using the .NET Provider for Oracle wouldn't hurt if you would got through with it =) HTH.

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Feb 10, 2011 at 1:24 PM

Thank you for your support.

As far as I know, it was decided to deprecate OracleClient as a part of Microsoft ADO.NET roadmap.

(http://blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx)

Anyway, Enterprise Library is great.

Feb 11, 2011 at 12:28 AM

Yes, it was deprecated in .NET 4.0.  We only tested it with System.Data.OracleClient to see if the behavior is something caused EntLib or the ODP.NET provider. 

You might want to ask this over in an ODP.NET forum.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com