Oracle Number mapped to Decimal

labate · Post by **labate** » Fri 15 Jun 2007 13:05

Hello,

I'm using OraDirect .NET 3.55.20.0 with Enterprise Library 2.0.

I have the following Oracle sp

procedure GetPersonByPersonId(in_personId in mspak.person.personid%type, out_cursor out T_CURSOR)

returning a cursor where one of the fields correspond to a Oracle column whose type is:

personid number not null

With the DataTableReader, when I do

idx = reader.GetOrdinal("personid");
if (!reader.IsDBNull(idx)) person.PersonId = reader.ToInt32(idx);

It fails and I have to do instead

idx = reader.GetOrdinal("personid");
if (!reader.IsDBNull(idx)) person.PersonId = Convert.ToInt32(reader.GetDecimal(idx));

It seems that all Oracle integer types are mapped to a .NET Decimal type.

Is it the correct behavior, or is there a way to directly use GetInt32?

Thanks
Adriano

Alexey · Post by **Alexey** » Mon 18 Jun 2007 08:36

It fails and I have to do instead

How does it fail? Please specify. I tested your code reading data from the table dept and haven't got any error. If the problem occurs only with enterprise library, stored procedures and ref cursors, please provide me with a test project and your database objects definitions.

labate · Post by **labate** » Mon 18 Jun 2007 09:17

Hello,

The exception stack trace is not very informative:

System.InvalidCastException: Specified cast is not valid.
à System.Data.DataTableReader.GetInt32(Int32 ordinal)
à Polyright.xxx.yyy.DatabaseAccess.GetAccountByPersonId(Int32 personId) dans ...\DatabaseAccess.cs:ligne ...

This occurs when I do:

Code: Select all

        public Account GetAccountByPersonId(int personId) {
            try {
                DbCommand dbCommand = database.GetStoredProcCommand("xxx.GetAccountByPersonId");

                database.AddParameter(dbCommand, "personId", DbType.Int32, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, personId);
                dbCommand.Parameters.Add(CreateCursorParameter("cursor"));

                IDataReader reader = database.ExecuteDataSet(dbCommand).CreateDataReader();

                Account account = null;
                int idx;

                if (reader.Read()) {
                    account = new Account();

                    // CORRECT : Use reader.GetDecimal()
                    idx = reader.GetOrdinal("accountId");
                    if (!reader.IsDBNull(idx))  account.AccountId = Convert.ToInt32(reader.GetDecimal(idx));

                    // ERROR : InvalidCastException
                    idx = reader.GetOrdinal("accountstatusid");
                    if (!reader.IsDBNull(idx))  account.AccountStatusId = reader.GetInt32(idx);

                    ...
                }

                return account;
            } catch (Exception e) {
                log.Error("GetAccountByPersonId : un problème est survenu", e);
                throw;
            }
        }

Although I use Enterprise Library 2.0, the underlying database provider is OraDirect .NET 3.55.20.0. I don't know where is the responsability of doing the data types mapping.

In the code sample above, the two fields are NUMBER columns in Oracle and I thought it makes sense to call GetInt32, but for all Oracle number columns, even for a double, I have to call GetDecimal() and then use Convert.ToInt32 or Convert.ToDouble.

Sorry, I have not the time to build a sample project, and it is not a blocking problem.

Thanks

Alexey · Post by **Alexey** » Tue 19 Jun 2007 08:15

Could you at least provide me with your database objects definitions?

labate · Post by **labate** » Tue 19 Jun 2007 08:48

I've sent you the requested information directly to your email address.

Adriano

Alexey · Post by **Alexey** » Tue 19 Jun 2007 09:16

OK, I'll take a look.