oracleLoader could not load number(9, 0) when I use oci v19

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
yan
Posts: 6
Joined: Tue 21 Jul 2020 11:11

oracleLoader could not load number(9, 0) when I use oci v19

Post by yan » Tue 21 Jul 2020 11:42

I try to use oracleLoader load data to DB, if oracle client version is 19c, I will get exception:
Loader error: row -1, col 0.

If oracle client version is 12c, everything is OK.

Here are the demo table:
table:
CREATE TABLE task
(
"test_number" NUMBER(9,0)
)

CREATE TABLE task_bak
(
"test_number" NUMBER(9,0)
)

data in table task
1

Here are the demo code:
OracleConnection connection = new OracleConnection("user id=ly_test;password=passwd;server=XXX;Unicode=true;connection timeout=30");
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;

cmd.CommandText = "select * from task";

try
{
using (var loader = new OracleLoader("task_BAK", connection))
{
loader.CreateColumns();
loader.Open();
using (var reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
var data = new object[reader.FieldCount];

reader.GetValues(data);

for (int i = 0; i < data.Length; i++)
loader.SetValue(i, data);

loader.NextRow();

count++;
}

loader.Close();
}
}
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
connection.Close();
}

I also get some work around:
1, if I change column type to {number(s,0), s > 9} or number, data could be loaded
2, if I change OracleDbType from integet to number, data could be loaded.
for (int i = 0; i < loader.Columns.Count; i++)
{
if (loader.Columns.OracleDbType == OracleDbType.Integer)
loader.Columns.OracleDbType = OracleDbType.Number;
}

I also test it with newest devart version, but this issue still exists.
Maybe this is bits mismatch issue I guess.
Can you confirm about this issue and can you tell me why this issue happened?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Sat 25 Jul 2020 17:27

Thank you for your report. We will investigate the question and notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Fri 31 Jul 2020 13:01

The bug with loading the Int32 value into a NUMBER column using OracleLoader in the Direct Path Load mode via Oracle Client 19 is fixed in the new build v9.12.1064.

yan
Posts: 6
Joined: Tue 21 Jul 2020 11:11

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by yan » Tue 04 Aug 2020 05:39

But I use default way to load data, it's not in the Direct Path load mode, Can you check if this issue fixed in indirectly path load mode?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Thu 06 Aug 2020 18:01

yan wrote: Tue 04 Aug 2020 05:39 But I use default way to load data, it's not in the Direct Path load mode
Direct Path Load is a default mode. Please upgrade to v9.12.1064.

yan
Posts: 6
Joined: Tue 21 Jul 2020 11:11

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by yan » Mon 10 May 2021 07:52

Hi, I have updated devart version to v9.13.
But when I try to use access as data source, I set one column data type as number, set its field size as Byte, this issue still exists.

Can you double check this?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Wed 19 May 2021 15:19

yan wrote: Mon 10 May 2021 07:52 Hi, I have updated devart version to v9.13.
But when I try to use access as data source, I set one column data type as number, set its field size as Byte, this issue still exists.

Can you double check this?
We have checked your code from the original post, it works with dotConnect for Oracle v9.14.1234 via Oracle Client 19 and Direct Mode. Please upgrade to v9.14.1234. If this doesn't help, send us an updated sample for reproducing the issue.

yan
Posts: 6
Joined: Tue 21 Jul 2020 11:11

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by yan » Tue 25 May 2021 02:20

Can you confirm if this issue exists in v9.13 or not?

And we also found the data length will be changed if we use my demo in v9.13.
Like number(3,0), number(5, 0) will turn to be number(9, 0), Can you help me check about this behavior in v9.14?

Thank you.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Tue 25 May 2021 14:28

yan wrote: Tue 25 May 2021 02:20 Can you confirm if this issue exists in v9.13 or not?
v9.13 works OK.
yan wrote: Tue 25 May 2021 02:20 And we also found the data length will be changed if we use my demo in v9.13.
Like number(3,0), number(5, 0) will turn to be number(9, 0), Can you help me check about this behavior in v9.14?
You can map datatype manually to one of the OracleDbType values:

Code: Select all

    loader.Columns[0].OracleDbType = OracleDbType.Byte;
Refer to https://www.devart.com/dotconnect/oracl ... bType.html.

yan
Posts: 6
Joined: Tue 21 Jul 2020 11:11

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by yan » Thu 27 May 2021 09:50

But I test it in my machine, both direct mode and indirect mode, I will get invalid cast Exception.

Here are my code:

Code: Select all

    static void Main(string[] args)
    {
      string connectionString = "Dsn=XXX;Driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=C:\\XXX.accdb;description=XXX;driverid=25;fil={MS Access;};safetransactions=0";
      OdbcConnection conn = new OdbcConnection(connectionString);
      conn.Open();
      DbConnection connectionSource = conn as DbConnection;
      var cmd = connectionSource.CreateCommand();
      cmd.CommandText = "select * from lytest_table";

      //OracleConnection connection = new OracleConnection("user id=ly_source;password=PWD;server=YYY;Unicode=true;connection timeout=30");
      //connection.Open();

      OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
      oraCSB.Direct = true;
      oraCSB.Server = "YYY";
      oraCSB.Port = 1521;
      oraCSB.ServiceName = "YYY";
      oraCSB.UserId = "ly_source";
      oraCSB.Password = "PWD";
      OracleConnection connection = new OracleConnection(oraCSB.ConnectionString);
      connection.Open();  

      try
      {
        using (var loader = new OracleLoader("LY_Access_test", connection))
        {
          loader.CreateColumns();
          loader.Open();
          using (var reader = cmd.ExecuteReader())
          {
            int count = 0;
            while (reader.Read())
            {
              var data = new object[reader.FieldCount];

              reader.GetValues(data);

              for (int i = 0; i < data.Length; i++)
                loader.SetValue(i, data);

              loader.NextRow();

              count++;
            }

            loader.Close();//-----throw exception on this line
          }
        }
      }
      finally
      {
        if (connection.State == System.Data.ConnectionState.Open)
          connection.Close();
      }

    }
Access Source:
table: lytest_table
columns_name/data_type/FieldSize:
ID/AutoNumber/long Integer
ly_id/Number/Byte
ly_name/short text/255

data:
1/0/ly_0

Dest:
table:LY_Access_test
CREATE TABLE LY_ACCESS_TEST
( "AUTO_ID" NUMBER(9,0),
"LY_ID" NUMBER(9,0),
"LY_NAME" VARCHAR2(300 BYTE)
)

Exception:
An unhandled exception of type 'System.InvalidCastException' occurred in Devart.Data.dll
Additional information: Unable to cast object of type 'System.Object[]' to type 'System.IConvertible'.

dotConnect for Oracle: v9.13.1127

Can you reproduce this issue in v9,13?
Is it fixed in v9.14?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Thu 27 May 2021 13:12

yan wrote: Thu 27 May 2021 09:50 Exception:
An unhandled exception of type 'System.InvalidCastException' occurred in Devart.Data.dll
Additional information: Unable to cast object of type 'System.Object[]' to type 'System.IConvertible'.

dotConnect for Oracle: v9.13.1127

Can you reproduce this issue in v9,13?
v9.13.1127 works OK. To fix the error you have encountered, replace

Code: Select all

              for (int i = 0; i < data.Length; i++)
                loader.SetValue(i, data);
with

Code: Select all

              for (int i = 0; i < data.Length; i++)
                loader.SetValue(i, data[i]);
If there is any other issue, specify the full stack trace of an error.

yan
Posts: 6
Joined: Tue 21 Jul 2020 11:11

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by yan » Fri 28 May 2021 06:25

Oh, yes, that's my fault.

I re-test it.

Result:
oci v19 + dotConnect for Oracle: v9.13.1127 + indirect mode
Error: invalidCastException

oci v12 + dotConnect for Oracle: v9.13.1127 + indirect mode
Succeed

dotConnect for Oracle: v9.13.1127 + indirect mode
Succeed



Can you help me test this in v9,14? If exists, will you fix this in the future?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: oracleLoader could not load number(9, 0) when I use oci v19

Post by Shalex » Mon 31 May 2021 17:12

yan wrote: Fri 28 May 2021 06:25 Result:
oci v19 + dotConnect for Oracle: v9.13.1127 + indirect mode
Error: invalidCastException
v9.13.1127 works successfully with Oracle Client v19.3.0.0 x64. Try reinstalling your Oracle Client.

Post Reply