> 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 spreadsheet 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.

> tommaso.gasta***@uniroma1.it wrote:
> > have you made sure you send the 5 commands in sequence - without closing
> > the connection - between a command and the following one?
>
> Hi tom...
>
> Thanks for the reply.  I don't think I've closed the connection.  Here
> is the complete code (this is a Console application, by the way):
>
> Imports System.Data.OracleClient ' for this to work, need to have
> System.Data.OracleClient listed as a reference in the project
> properties...
>
> Module Module1
>
>     Sub Main()
>
>         Dim dbConnectionString As String = "Password=ASHLEY;User
> ID=ASHLEY;Data Source=HAGRID"
>         Dim queryString As String = "SELECT * FROM TEST"
>
>         Dim dbConnection As OracleConnection
>         Dim dbCommand As OracleCommand
>         Dim dbDataReader As OracleDataReader
>
>         Try
>             dbConnection = New OracleConnection(dbConnectionString)
>             dbConnection.Open()
>
>             dbCommand = dbConnection.CreateCommand()
>             dbCommand.CommandText = "SET TRANSACTION READ ONLY NAME
> 'PDI_HSD'"
>             dbCommand.ExecuteNonQuery()
>
>             dbCommand.CommandText = queryString
>
>             Console.WriteLine("First query...")
>             dbDataReader = dbCommand.ExecuteReader()
>             Do While dbDataReader.Read()
>                 Console.WriteLine(dbDataReader(0))
>             Loop
>             dbDataReader.Close()
>
>             Console.WriteLine("Change the database table contents using
> an external tool, then press return.")
>             Console.ReadKey()
>
>             Console.WriteLine("Second query...")
>             dbDataReader = dbCommand.ExecuteReader()
>             Do While dbDataReader.Read()
>                 Console.WriteLine(dbDataReader(0))
>             Loop
>             dbDataReader.Close()
>
>             Console.WriteLine("Expecting the two queries to give the
> same results, despite the external modification.")
>             Console.WriteLine("Press return to finish.")
>             Console.ReadKey()
>
>             dbConnection.Close()
>
>         Catch ex As Exception
>             Console.WriteLine(ex.Message)
>         End Try
>
>     End Sub
>
> End Module
>
> Hopefully the prompts make it clear how I have been testing this.  When
> I test it here, the change I make with an external tool when prompted
> is shown in the second query: so it seems to me that the SET
> TRANSACTION READ ONLY has had no effect.
>
> Any more help appreciated.
>
> Ashley.

> Hi tom -- thanks for the reply.
>
> tommaso.gasta***@uniroma1.it wrote:
> > as first suggestion, irrelevant to your problem, I would move some
> > clean up in a finally block to avoid leaving it open in case of
> > exceptions
>
> Yes, a good idea -- thanks.
>
> > back to the problem I do not have here Oracle. I will make some
> > hypotheses to be tested.
> >
> > Is it possible that the external tool does a COMMIT which resets the
> > SET TRANSACTION READ ... ?
>
> Yes, the external tool does do a COMMIT.  But that shouldn't reset the
> SET TRANSACTION READ ONLY.  When I test manually (substituting an
> sqlplus session for the VB program), any changes I make in the external
> tool don't show up in the sqlplus session after the SET TRANSACTION,
> COMMIT or not.
>
> > I also have a curiosity: have you measured any performance improvement
> > by using an
> > OracleConnection instead of an OleDbConnection ?
>
> I haven't tried OleDbConnection yet.
>
> Ashley.