2
0
During this post we will talk about, how to use ROLLBACK or COMMIT inside the JavaScript procedure. Though this post would be very basic for majority of the folks but still worthful in case of migration. While working on the migration project i.e., moving Procedures from Oracle to Snowflake we see ROLLBACK and COMMIT very often in source. Sometimes we see challenges in implementing these commands in Snowflake JavaScript procedure. By mistake we use them inside procedure in similar fashion like we did in oracle i.e. COMMIT;. Hence while executing the code, we get the following error :
try {
commit;
}
Failed: ReferenceError: commit is not defined.
Similar way if we use direct ROLLBACK inside the procedure we come across with below error.
catch (err) {
rollback;
}
Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
Why this happens: This does occurs when you run the procedure that has explicit START TRANSACTION, but no explicit “ROLLBACK”.
Moreover, Error handling in Oracle is taken care by BEGIN-EXCEPTION-END Block and place the ROLLBACK inside the EXCEPTION clause. To achieve the same in Snowflake we use try -catch block and keep the ROLLBACK inside the Catch block.
PRactical details
Lets have some practical details.
Say we have student_profile table which contains 5 records.
Requirement:
- Insert a new record with ID= 6 in table.
- Delete record with ID = 1 from table
- Delete record from nonexistent table (to capture the error) .
- Insert a new record with ID= 7 in table.
Expected Output:
Procedure will create a new record with ID = 6 and delete the ID = 1 record. But when it tries to delete the record from nonexistent table an error would be thrown. Error would be captured in Catch block and entire DML transaction should be Roll back. State of the table should remains as-is as it was before the proc run.
Please note below two statements:
snowflake.execute( {sqlText: “commit;”} );
snowflake.execute( {sqlText: “rollback;”} );
Please note, After execution the procedure, state of Table remained same.
