Effects of Transaction Commits on Result Sets and REF CURSORs

When processing result sets generated from executing statements and creating REF CURSORs, the behavior when transactions in TimesTen connections are committed differs from that when transactions in Oracle Database connections are committed.

When a transaction is committed in TimesTen while a result set of an OracleDataReader object is open, the result set is closed automatically, unlike in an Oracle database. This applies to explicit commits, autocommit, and implicit commits.

In TimesTen, an implicit commit occurs after a DDL statement. In ODP.NET, an implicit commit also occurs when an OracleCommand object is executed without there first being an OracleTransaction object instantiated from the command connection. An explicit commit occurs when the Commit method is called on an OracleTransaction object. In either case, if a commit occurs in a TimesTen connection before a result set that is open in the transaction is completely processed, the Function sequence error exception may be thrown.

This difference in behavior is likely to occur when the execution of an OracleCommand object is interleaved with the processing of a result set associated with another OracleCommand object. To avoid the Function sequence error exception, the execution and processing of a result set should be contained exclusively within the context of an OracleTransaction object. This prevents a commit from occurring before all rows of the result set are retrieved.

The occurrence of a Function sequence error exception may depend on the value of the FetchSize property of an OracleCommand, OracleRefCursor or OracleDataReader object. If the FetchSize property is not explicitly set or if it is set to a large value, then many rows may be fetched by the application before the Function sequence error exception is thrown.