Command Parameters with direct mode

[akrora]

Hello,

I use the standard edition of dotConnect for Oracle and tried the following code once with direct=true in the connection string, and once with direct=false :

*******
OracleConnection con = new OracleConnection(connectionString);

con.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;

cmd.CommandText = "insert into T_PROJECT_SESSION(C_USER_ID,C_TIMESTAMP,C_PROJECT_ID,C_PROJECT_SESSION_ID)values(:p1,:p2,:p3,SEQ_T_PROJECT_SESSION.nextval);"
+"select SEQ_T_PROJECT_SESSION.CurrVal into :p0 from DUAL;\n";

cmd.CommandText += "insert into PATENTREPORT.T_TEST_PARAM(PARAM)values(:p0);\n";

cmd.CommandText = "declare\n begin\n" + cmd.CommandText + " end;";

// Parameters

OracleParameter param0 = cmd.CreateParameter();
param0.DbType = System.Data.DbType.Int32;
param0.Direction = System.Data.ParameterDirection.Output;
param0.ParameterName = "p0";
param0.Value = null;
cmd.Parameters.Add(param0);

OracleParameter param1 = cmd.CreateParameter();
param1.DbType = System.Data.DbType.String;
param1.Direction = System.Data.ParameterDirection.Input;
param1.ParameterName = "p1";
param1.Value = "user";
cmd.Parameters.Add(param1);

OracleParameter param2 = cmd.CreateParameter();
param2.DbType = System.Data.DbType.Date;
param2.Direction = System.Data.ParameterDirection.Input;
param2.ParameterName = "p2";
param2.Value = DateTime.Now;
cmd.Parameters.Add(param2);

OracleParameter param3 = cmd.CreateParameter();
param3.DbType = System.Data.DbType.Int32;
param3.Direction = System.Data.ParameterDirection.Input;
param3.ParameterName = "p3";
param3.Value = 847;
cmd.Parameters.Add(param3);

cmd.ExecuteNonQuery();

con.Commit();
con.Close();

*********
The first Insert works fine in both modes. But then it seems that in direct mode the :p0 value is Null after the SELECT INTO statement. With direct=false, the value of this parameter is the correct one (the SEQ_PROJECT_SESSION.Currval).

I red something about a bug with repeated parameters using direct mode ? Is this the problem ?
If it isn't, how can I do to make this code work correctly ?
If this code cannot work in direct mode, is there a possibility to use the Devart assemblies with direct=false but no Oracle Client (for example add the oci.dll in our Deployment, or something like that) ?

The version of dlls I use are :
Devart.Data 5.0.1.0
Devart.Data.Oracle 5.20.29.0

Thanks in advance for your response.

[Shalex]

I have tried the following code with the 5.20.29 version of dotConnect for Oracle and the 10.2.0.1 version of Oracle server:

Code: Select all

      conn.Open();
      OracleCommand command = conn.CreateCommand();
      command.CommandText = "insert into table values (1);\n";
      command.CommandText += "select ID_SEQ.CurrVal into :p0 from dual;\n";
      command.CommandText = "begin\n" + command.CommandText + "end;";
      OracleParameter param0 = command.CreateParameter();
      param0.DbType = System.Data.DbType.Int32;
      param0.Direction = System.Data.ParameterDirection.Output;
      param0.ParameterName = "p0";
      command.Parameters.Add(param0);
      
      command.ExecuteNonQuery();
      
      conn.Close();

It works properly (the p0 parameter contains the actual value from the database) in both cases:
in the direct mode and with Oracle client. Please specify the version of Oracle server
you are using and send us (support at devart*com) a small test project that reproduces the problem.
Please include the definitions of database objects and avoid using third-party components.

[Shalex]

We have reproduced the problem. I will post here when the bug is fixed.

[AndreyR]

The issue with repeating parameters of OracleCommand in the Direct mode is fixed.
Look forward to the next build of dotConnect for Oracle that will be available in a week.

[Shalex]

The new build of dotConnect for Oracle 5.20.33 is available for download now.
It can be downloaded from 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://www.devart.com/forums/viewtopic.php?t=14909 .