Page 1 of 1
oracleLoader could not load number(9, 0) when I use oci v19
Posted: Tue 21 Jul 2020 11:42
by yan
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Sat 25 Jul 2020 17:27
by Shalex
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
Posted: Fri 31 Jul 2020 13:01
by Shalex
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
Posted: Tue 04 Aug 2020 05:39
by yan
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
Posted: Thu 06 Aug 2020 18:01
by Shalex
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Mon 10 May 2021 07:52
by yan
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Wed 19 May 2021 15:19
by Shalex
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Tue 25 May 2021 02:20
by yan
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Tue 25 May 2021 14:28
by Shalex
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Thu 27 May 2021 09:50
by yan
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Thu 27 May 2021 13:12
by Shalex
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.
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Fri 28 May 2021 06:25
by yan
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?
Re: oracleLoader could not load number(9, 0) when I use oci v19
Posted: Mon 31 May 2021 17:12
by Shalex
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.