Read-only transaction in Oracle

[Maxim Maslov]

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

 

[Sahil Malik]

I believe ODP.NET does.

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

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

 

[David Browne]

I believe ODP.NET does.

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

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

 

[Sahil Malik]

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

 

[Yan-Hong Huang[MSFT]]

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.