Transactions not working for SubSonic under Oracle?

Question

The following code sample works perfectly under SQL Server 2005:

using (TransactionScope ts = new TransactionScope()) {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope()) {
                MyTable t = new MyTable();
                t.Name = "Test";
                t.Comments = "Comments 123";
                t.Save();
                ts.Complete();
            }
        }

But under Oracle 10g it throws a "ORA-02089: COMMIT is not allowed in a subordinate session" error. If I only execute the code inside the SharedDbConnectionScope block then everything works OK, but obviously I won't be able to execute operations under a transaction, thus risking data corruption.

This is only a small sample of what my real application does. I'm not sure as to what may be causing this behavior; anyone out there care to shed some light on this issue please?

Many thanks in advance.

Answer 1

Looking up that specific error message suggests that something may be trying to explicitly or implicitly COMMIT.

If you look at OracleDataProvider.cs, inside GetInsertSql(Query qry) an explicit COMMIT is issued:

    if (retrieveID)
        insertSQL.AppendFormat("  RETURNING {0} INTO :lllhhhmmm; COMMIT; END; ", qry.Schema.PrimaryKey.ColumnName);

COMMIT is also called in ExecuteScalar() in the same file. Do you still see the problem when you are updating records instead of inserting them?

Answer 2

please send me your appconfig code and which version you are using for oracle ??