OracleCommand.ExecuteArray(int iters) Bug

[isutommyt]

Greetings,

I'm experiencing some issues with the OracleCommand.ExecuteArray(int iters) method when connecting via direct mode. I'm trying to bulk insert some data that includes some null or empty cells. When in direct mode, the method seems to skip over these parameters and ends up inserting the next parameter into the column, ie shifting the data.

When not in direct mode the method works great. I'm using the 7.2.122 version. Any insight here? If necessary I can work up an example when I get a bit more time.

Thanks,
Jason

[isutommyt]

Here are some more details with some test code:

Create a table with 3 columns
Create an OracleCommand
Add the CommandText and the OracleParameters

Notice how the first parameter has values that are all null. If just one of the 2 values is changed to a non null value, then both direct and non direct queries work. If both values are null like the code below, then only the non direct connection mode will work.

Code:

Code: Select all

            
OracleConnection conn = new OracleConnection("User Id=TESTUSER;Password=TESTPASSWORD;Server=TESTSERVER");
            //OracleConnection conn = new OracleConnection("User Id=TESTUSER;Password=TESTPASSWORD;Server=server1.test.com;Direct=True;Sid=TESTSERVER;Port=1521;");
            conn.Open();

            OracleCommand cmd = conn.CreateCommand();

            try
            {
                cmd.CommandText = "DROP TABLE DEVARTBULKLOAD";
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }

            try
            {
                cmd.CommandText = "create table devartBulkLoad (colA varchar2(100 BYTE), colB number(10,2), colC varchar2(100 BYTE))";
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }

            OracleCommand bulkCommand = conn.CreateCommand();
            bulkCommand.CommandText = "insert into devartbulkload (cola,colb,colc) values (:cola,:colb,:colc)";

            OracleParameter par1 = new OracleParameter("COLA", OracleDbType.VarChar);
            par1.IsNullable = true;
            object[] paramValues = new object[2];
            paramValues[0] = null;
            paramValues[1] = null;
            par1.Value = paramValues;
            bulkCommand.Parameters.Add(par1);

            OracleParameter par2 = new OracleParameter("COLB", OracleDbType.Number);
            par2.IsNullable = true;
            paramValues = new object[2];
            paramValues[0] = 3;
            paramValues[1] = 2;
            par2.Value = paramValues;
            bulkCommand.Parameters.Add(par2);

            OracleParameter par3 = new OracleParameter("COLC", OracleDbType.VarChar);
            par3.IsNullable = true;
            paramValues = new object[2];
            paramValues[0] = "foo";
            paramValues[1] = "bar";
            par3.Value = paramValues;
            bulkCommand.Parameters.Add(par3);

            bulkCommand.ExecuteArray(2);

Non Direct Connection Table Result:


Everything looks good!

Direct Connection Table Result:


Some values are shifted and we get some weird text! Can you please look into this issue?

Thanks,
Jason

[Pinturiccio]

We could not reproduce the issue. We execute your code without any changes except the credentials in connection strings and it works fine in our environment in both Direct and OCI modes.

Please specify the following for reproducing the issue:
a) the exact version of your Oracle server (xx.x);
b) the NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET parameters of your Oracle server;
e) the name, version of your operating system and its regional settings: Control Panel > Regional and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs.

[isutommyt]

Thanks for looking into, here is the information you have requested.

a) the exact version of your Oracle server (xx.x);
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

b) the NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET parameters of your Oracle server;
NLS_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

e) the name, version of your operating system and its regional settings: Control Panel > Regional and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs.
Windows 7 Enterprise x64 SP1

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[isutommyt]

May I get an update on the progress of the bug fix and potential release? Thanks!

[Pinturiccio]

Our investigation is still in progress. We will notify you as soon as we have some results.

[Pinturiccio]

We have fixed the bug with the ExecuteArray method when connecting to the UTF8 (Unicode) database in the Direct mode. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

The new build of dotConnect for Oracle 7.5.164 is available for download now!
It can be downloaded at http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=25664

[isutommyt]

My testing no longer produces the bug! Thanks for the fix.