0

I was using System.Data.OracleClient and there is a stored procedure which uses ExecuteNonQuery(). Now I am using Oracle.ManagedDataAccess.Client so my question is can I use ExecuteNonQuery() in Oracle.ManagedDataAccess.Client?

My previous code was

   Database db = DatabaseFactory.CreateDatabase();
   DbCommand objComm =  db.GetStoredProcCommand("package_name.sp", ab, ab1, ab2, ab3, ab4, ab5, ab6);
   var result = new Collection<Alarm>();
   db.ExecuteNonQuery(objComm);

Now my code is

  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = commandText;
  cmd.CommandType =CommandType.StoredProcedure;
  cmd.Parameters.Add("ab", OracleDbType.Varchar2, ParameterDirection.Input);
  cmd.Parameters.Add("ab1", OracleDbType.Varchar2, ParameterDirection.Input);
  cmd.Parameters.Add("ab2", OracleDbType.Varchar2, ParameterDirection.Input);  
  cmd.Parameters.Add("ab3", OracleDbType.Varchar2, ParameterDirection.Input);
  cmd.Parameters.Add("ab4", OracleDbType.Varchar2, ParameterDirection.Input);
  cmd.ExecuteNonQuery();

Will this updated code will work?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ratna
  • 124
  • 4
  • 18

2 Answers2

2

Be aware the .BindByName defaults to false.

When not binding parameters by name, Oracle.ManagedDataAccess binds the parameters in the order in which you execute the cmd.Paramaters.Add() calls.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
The Grinch
  • 21
  • 2
1

Absolutely, I'm in the process of converting from Oracle.DataAccess/System.Data.OracleClient to Oracle.ManagedDataAccess.

A few gotchas I've found in the process:

1.  Make sure you specify your data type in the parameters, otherwise, it has a tendency to fail.
2.  An older version of managed has a CLOB bug that I ran into when you try to select data.  Make sure you get the latest from github.
3.  By default, the tnsnames/ldap/sqlnet location defaults to the application directory in the managed situation.  If you assign ORACLE_HOME environmental variable at the windows level, it'll fix that.
4.  Does the server have the full version of Oracle client?  Do they have both a 64-bit and 32-bit version?  You'll want to make sure you set the environmental variable in the application then to and use the right one, otherwise you'll run into Crystal issues.
5.  Long running executions sometimes have issues if you are using Oracle database 11.1 - https://stackoverflow.com/questions/29847444/odp-net-oracle-manageddataaccess-causes-ora-12537-network-session-end-of-file

Example Parameter Add

cmd.Parameters.Add("parametername", OracleDbType.Varchar2, 
  parametervalue, ParameterDirection.Input)

Remember that Oracle.ManagedDataAccess inherits/implements a structure from the same classes/interfaces as System.Data.OracleClient, so you'll see a lot of similarities, but it won't be identical.

Ctznkane525
  • 7,297
  • 3
  • 16
  • 40