Skip to Content
Previous

Using Autonomous Transactions

By Craig Cmehil

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.

Details

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”.

save
Please log in to access this content.
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.

HRTT

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
Please log in to access this content.
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
Please log in to access this content.
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.

results
Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

15 Min.

Intermediate

Prerequisites

Next
Back to top