Our application uses some fairly complex PL/SQL procedures to build reports in temporary tables so that we can access the data as a ref cursor and bind to a grid. We built the tables using “on commit delete rows” when creating the Oracle table. See DBASupport.com for a quick explanation of Oracle's temporary tables. But calling commit inside the PL/SQL package did not delete the rows as implied by the Oracle documentation. The new data on subsequent calls simply added to what already existed in the table. We are using an Oracle Provider version of the Data Access Application Block, and called the stored procedure like this:
DataResults = Daab.ExecuteDataTable(CommandType.StoredProcedure, ProcName, ProcParametersArray)
To solve the problem, we wrapped the call to the stored procedure in a transaction, even though the procedure only issued select sql statements.
Dim FakeTransaction As OracleTransaction
FakeTransaction = Conn.BeginTransaction(IsolationLevel.Serializable)
DataResults = Daab.ExecuteDataTable(FakeTransaction, CommandType.StoredProcedure, ProcName, ProcParameters)
FakeTransaction.Rollback()
Oddly enough, calling .Commit() did not work as implied by the “on commit delete rows“ command added when creating the table. The data persisted in the table. Calling .Rollback() worked though, as I would expect for any transaction. I was unable to find documentation to tell if there is any perfomance drawback to using IsolationLevel.Serializable versus IsolationLevel.ReadCommitted or IsolationLevel.Unspecified as all three give the desired effect on the temporary table.