Leveraging SQLScript in Stored Procedures & User Defined Functions through the use of Autonomous transactions
You will learn
The autonomous transaction is independent from the main procedure transaction. Changes made and committed by an autonomous transaction can be stored in persistency regardless of commit/rollback of the main transaction. The end of the autonomous transaction block has an implicit commit.
Please note - This tutorial is based on SPS11
Return to the procedure called get_product_by_filter.
Remove the COMMIT statement, and instead wrap the INSERT statement with an AUTONOMOUS TRANSACTION block as shown.
Use what you have learned already and perform a build on your hdb module. Then return to the HRTT page and make sure the input parameters are as shown and run the CALL statement again.
You will still get the error for invalid column. Select the SELECT statement for the log.errors table and click “Run” to execute it.
You will now notice that a new row was entered into the log.errors table
Select the SELECT statement for the log.messages table and click “Run” to execute it.
Another “Chuck” record was not inserted. “Chuck Norris’s” record was removed by the rollback (“Is that even possible…?”) by using AUTONOMOUS TRANSACTION blocks, the code within is isolated from the rest of the mainline code and is treated as a separate transaction.