oracleLoader could not load number(9, 0) when I use oci v19
oracleLoader could not load number(9, 0) when I use oci v19
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?
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
Thank you for your report. We will investigate the question and notify you about the result.
Re: oracleLoader could not load number(9, 0) when I use oci v19
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
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?
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
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.
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
v9.13 works OK.
You can map datatype manually to one of the OracleDbType values:
Code: Select all
loader.Columns[0].OracleDbType = OracleDbType.Byte;
Re: oracleLoader could not load number(9, 0) when I use oci v19
But I test it in my machine, both direct mode and indirect mode, I will get invalid cast Exception.
Here are my code:
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?
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();
}
}
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
v9.13.1127 works OK. To fix the error you have encountered, replaceyan 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?
Code: Select all
for (int i = 0; i < data.Length; i++)
loader.SetValue(i, data);
Code: Select all
for (int i = 0; i < data.Length; i++)
loader.SetValue(i, data[i]);
Re: oracleLoader could not load number(9, 0) when I use oci v19
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?
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?