The value of parameter is not sent to Oracle

[zhuqijun]

Hello Support,

Our product is using dotConnect 7.7.276 to connect to the Oracle database.

Our customers' DBA reported that he observed the insert statement only send the SQL to the oracle, without sending the value of the parameter, which makes the oracle waiting for the value of the parameter.

My question is: is the DBA's observation reasonable? Does DotConnect send the SQL and the value of the parameter separately?

Thanks!

[Pinturiccio]

My question is: is the DBA's observation reasonable? Does DotConnect send the SQL and the value of the parameter separately?

If the Prepare method of the OracleCommand object was not executed before command execution, the command and parameters are sent to the database as one packet. The Prepare method creates a prepared (or compiled) version of the command on the server.

However, parameters of the LOB and Object types are an exception of this rule. Parameters, having such type, are sent in separate packets even if the Prepare method was not called.

[zhuqijun]

Thanks for you reply.

Do you know any possibility or bugs that DotConnect only send the SQL to Oracle but not send the value of the parameter?

Our customer insists that the Oracle only received the SQL but not received the value of parameter, so the Oracle keeps waiting.

[Pinturiccio]

Do you know any possibility or bugs that DotConnect only send the SQL to Oracle but not send the value of the parameter?

We have no knowledge of such bug.

Our customer insists that the Oracle only received the SQL but not received the value of parameter, so the Oracle keeps waiting.

Try using the last version of dotConnect for Oracle. 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). Is the issue reproduced with this verison?

If you can, please provide us with the flowing information:

1. Please create a small test project which can reproduce the issue and send it to us.
2. DDL/DML scripts of the table, that are required for execution of the application.
3. Oracle server version.
4. If you use OCI connection mode, specify the version of Oracle Client.
5. Also, please specify the way you determine that the command text was sent to the server, and parameters were not.

[zhuqijun]

Our code is like this:

OracleCommand cmd = new OracleCommand();
cmd.Connection= con;
cmd.CommandText = "insert into sourcedatatable(col1,COL_INT) values(:col1,:COL_INT)";

OracleParameter parameter1 = cmd.CreateParameter();
parameter1.ParameterName = "col1";
parameter1.Value = new string[] { "123", "456" };

OracleParameter parameter2 = cmd.CreateParameter();
parameter2.ParameterName = "COL_INT";
parameter2.Value = new int[] { 1,3 };

cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
con.Open();
int result = cmd.ExecuteArray(2);

My question is: if DotConnect may send the request to Oracle in 2 packets, are all the 2 packages are sent after calling the line cmd.ExecuteArray ?

[Pinturiccio]

In your example, both command text and parameters should be sent as one packet when calling the ExecuteArray method. Why do you think that in your case parameters are sent as a separate packet?

My question is: if DotConnect may send the request to Oracle in 2 packets, are all the 2 packages are sent after calling the line cmd.ExecuteArray ?

Yes, packets are sent after executing the following line:

Code: Select all

int result = cmd.ExecuteArray(2);

[zhuqijun]

Ok, my previous only send one package.
If I add another parameter with the type of LOB or object type, it will send with 2 packages, rights?

And the packets are sent after executing the following line:
int result = cmd.ExecuteArray(2);

Correct?

[Shalex]

Correct.