MSCEWI3111
Transaction control statement requires additional intervention
Last updated
Transaction control statement requires additional intervention
Last updated
This is a deprecated version of the SnowConvert documentation, please visit the official site .
Medium
Snowflake does support control statements; however, there are some differences between the transaction handling between the two platforms, the major difference being the implicit transaction handling in Oracle, which Snowflake does not support. The second difference regards the default session variables' values: them being modifiable will change the desired migration for these statements.
In Oracle's , by default, are started whenever an executable statement is started and finished when there is either a Rollback or Commit. In Snowflake the "AUTOCOMMIT"
variable is set by default, meaning that each executable statement performs a transaction and then commits it on success, or rollbacks on failure.
Oracle's Simple and statements can be uncommented (see their Snowflake equivalents for and ), but then additional work must be done to mimic the original transaction behavior by adding "BEGIN TRANSACTION"
statements at the beginning of scope and then immediately after doing a Commit or a Rollback, and then making sure that each execution path does finish in either Rollback or Commit statement to avoid Snowflake errors due to unfinished Transactions.
More complex Rollback and Commit statements will need further refactoring since Snowflake supports neither "SAVEPOINTS"
nor "FORCE TRANSACTION"
functionalities, meaning these will need major refactoring.
The next code will commit insertions of 1, and rollback insertions of 2. On Snowflake, doing a Commit and Rollback perform no action since the statement already committed the insertion.
You can add "BEGIN TRANSACTION"
statements to mimic Oracle's implicit transactions.
Make sure all new transactions are finished when control ends to avoid errors on Snowflake.
If you need more support, you can email us at