Skip to Content

Using Autonomous Transactions

0 %
Using Autonomous Transactions

Using Autonomous Transactions

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

Step 1: Edit previous procedure

Return to the procedure called get_product_by_filter.

procedure editor

Remove the COMMIT statement, and instead wrap the INSERT statement with an AUTONOMOUS TRANSACTION block as shown.

insert statement

Click “Save”.

Step 2: Build and run `log.errors` SELECT

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.

select statement
Step 3: Run `log.messages` SELECT

You will now notice that a new row was entered into the log.errors table

new row

Select the SELECT statement for the log.messages table and click “Run” to execute it.

select statement
Step 4: Examine results

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.


Next Steps

Back to top