Hello All;

Does anybody know any way to start read-only Oracle transaction in .NET
application?
This mode can be set in PL/SQL by SET TRANSACTION READ ONLY statement, but
Oracle provider for .NET does not support such isolation level.

Thank you,
Maxim

I believe ODP.NET does.

If not - nothing prevents you from running an OracleCommand directly.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


Show quoteHide quote

Just be careful.

Unlike SqlServer, Oracle has no "autocommit" mode.  So if you don't start an
OracleTransaction the OracleClient will issue commits for you, ending your
read only transaction and starting a new one.

So start an OracleTransaction with the default isolation level, then issue
"SET TRANSACTION READ ONLY" through an oracle command.

Like this (this example is ODP.NET):

    using (OracleConnection con = connect())
    using (OracleTransaction trans = con.BeginTransaction())
    {
      new OracleCommand("set transaction read only",con).ExecuteNonQuery();
      //whatever

    }
David

Yes I agree, that is slightly upside down, so you've gotta keep that in
mind.

So the fact that ---- in Oracle SQL+, if I run Update, and don't do Commit -
essentially I did nothing, but in
comparison, in Sql Query Analyzer,  if I run Update, and exit - I did
actually make a change to the d/b.

-- makes sense now.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


Show quoteHide quote

Hi Maxim,

How is the status of the issue? Sahil and David have provided good
suggestions here. :) Also, I think OracleTransaction class can be used for
a transaction in Oracle.

public void RunOracleTransaction(string myConnString)
{
   OracleConnection myConnection = new OracleConnection(myConnString);
   myConnection.Open();

   OracleCommand myCommand = myConnection.CreateCommand();
   OracleTransaction myTrans;

   // Start a local transaction
   myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
   // Assign transaction object for a pending local transaction
   myCommand.Transaction = myTrans;

   try
   {
     myCommand.CommandText = "INSERT INTO Dept (DeptNo, Dname, Loc) values
(50, 'TECHNOLOGY', 'DENVER')";
     myCommand.ExecuteNonQuery();
     myCommand.CommandText = "INSERT INTO Dept (DeptNo, Dname, Loc) values
(60, 'ENGINEERING', 'KANSAS CITY')";
     myCommand.ExecuteNonQuery();
     myTrans.Commit();
     Console.WriteLine("Both records are written to database.");
   }
   catch(Exception e)
   {
     myTrans.Rollback();
     Console.WriteLine(e.ToString());
     Console.WriteLine("Neither record was written to database.");
   }
   finally
   {
     myConnection.Close();
   }
}

If you have any more concerns, please feel free to post here and we will
follow up. Thanks veyr much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
Register to Access MSDN Managed Newsgroups!
-http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
p&SD=msdn

This posting is provided "AS IS" with no warranties, and confers no rights.

Removing rows from a DataTable is VERY slow
SQLDataReader
XML Dataset
[BUG?] (2) Update database using stored procedure and OleDbDataAdapter.Update
Aborting a thread corrupts my SqlConnection (?)
data adapter update using datatable that has MANY changes
how to move record forward or backward
how to find out my ADO.NET version
newbie question.
Use of undocumented sp_MSforeachtable and MStablespace