Oracle read only transaction in VB .NET?

Tech-Archive recommends: Fix windows errors by optimizing your registry

I am attempting to write a program with VB 2005 Express Edition which
accesses an Oracle 9 database and dumps the results of three SELECT
queries into a spread*** file once every hour.

Modifications will be being made to the database whilst this program is
running. I would like the three queries to each give data relating to
the same point in time, despite the fact they actually run in sequence.

In Oracle's sqlplus tool, I can use "SET TRANSACTION READ ONLY" to give
transaction-level read consistency. Oracle describe it this way: "All
subsequent queries in that transaction only see changes committed
before the transaction began. Read-only transactions are useful for
reports that run multiple queries against one or more tables while
other users update these same tables".

This seems to be exactly what I need. I'm thinking that the structure
I want is something like the below (I think the detail of the SELECT
queries doesn't matter):

SET TRANSACTION READ ONLY;
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
COMMIT;

I've tested this with sqlplus, and if I modify the tables whilst the
transaction is in progress, the results show the state as it was when
the transaction began :) (Conversely, if I don't use a transaction,
intermediate modifications between the queries do have an effect.)

Unfortunately I can't get anything similar to work in Visual Basic
..NET. I have tried adding code something like the following before
some code that does two test queries with a pause in between, but the
SET TRANSACTION READ ONLY seems to have no effect. (Apologies for not
providing full code here.)

dbConnection.Open()
dbCommand = dbConnection.CreateCommand()
dbCommand.CommandText = "SET TRANSACTION READ ONLY"
dbCommand.ExecuteNonQuery()

I assume the SET TRANSACTION READ ONLY is being optimised away, or
perhaps changes made to the connection in this way aren't persistent.

I have also tried something along the lines of:

dbConnection.Open()
dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Snapshot)
dbCommand.Transaction = dbTransaction

because the description of IsolationLevel.Snapshot seems to be closest
to what I want. But with this, I get "Invalid IsolationLevel
parameter: must be ReadCommitted or Serializable." I assume
IsolationLevel.Snapshot just isn't implemented in
System.Data.OracleClient :(

Am I approaching this in the wrong way? Perhaps I need to work at a
lower level, eg ODBC?

I am a beginner at VB.NET. However I have a lot of experience in C,
Java, Apache+PHP+mysql and some experience in VB6.

Any help much appreciated.

Ashley.

.

Quantcast