Oracle Apps Modules

TCL in Oracle 12c: Transaction control Statement- SAVEPOINT, COMMIT and ROLLBACK

Transaction control Statement- SAVEPOINT, COMMIT and ROLLBACK

TCL (Transaction Control Statement) is used to manage transaction in databases. So first of all,

What is Transaction?

A transaction is a sequence of one or more SQL statements that databases treats as a unit: either all of the statements are performed or none of them.

In Real time scenarios, we need Transaction to model the business process so that several operations can be performed as a unit. For e.g. when an Manager leaves the organization then a row must be inserted into JOB_HISTORY  table to record when Manager left and for every Employees who reports to that manager value of MANAGER_ID must be updated in EMPLOYEES table. To model this we group the INSERT and UPDATE statements into a single transaction.

There are three types of TCL in SQL:

  • SAVEPOINT: Used to mark a save point in transaction, which can be rollback later.
  • COMMIT: It ends the current transaction and make the permanent changes.
  • ROLLBACK: Used to undo (or rollback) an unsaved transaction either entirely or to the specified SAVEPOINT.

In SQL Developer:

Transaction control Statement- SAVEPOINT, COMMIT

Transaction control Statement- SAVEPOINT, COMMIT

 

 

Note: If you do not commit a transaction explicitly and the programs terminates explicitly then the database automatically rolls back to the last uncommitted transaction.


Committing a Transaction

Committing a transaction makes permanent changes, delete all Save-point and release the database locks.

BEFORE Transaction

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Transaction control Statement- SAVEPOINT, COMMIT

Transaction: add row to tables

INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');

Transaction control Statement- SAVEPOINT, COMMIT

Check for the added row

Transaction control Statement- SAVEPOINT, COMMIT Oracle 12c tcl

Commit the transaction:

Transaction control Statement- SAVEPOINT, COMMIT oracle 12c TCL


Using Rollback: Rolling Back the Transaction

It undoes the changes. One can roll back entire current transaction or can roll back to a particular save-point.

1.Rollback the current transaction to specified savepoint.

Use the ROLLBACK statement with the TO SAVEPOINT clause.

  • Does not end the transaction
  • Reverses only the changes made after the specified savepoint
  • Erases only the savepoints set after the specified savepoint (excluding the specified savepoint itself)

Releases all table and row locks acquired after the specified savepoint.

2.Rollback the entire current transaction:

Use either the ROLLBACK statement or in sql developer environment click on rollback icon.

  • Ends the transaction
  • Reverses all of its changes
  • Erases all of its savepoints.
  • Releases any transaction locks

Before transaction:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Transaction control Statement- SAVEPOINT, COMMIT oracle 12c TCL

Running a Transaction (updating the table):

UPDATE REGIONS

SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

Transaction control Statement- SAVEPOINT, COMMIT oracle 12c TCL

Check the Changes: Region name changes from ‘Middle East and Africa’ to ‘Middle East’.

Transaction control Statement- SAVEPOINT, COMMIT oracle 12c TCL

Rollback transaction:

ROLLBACK;

Transaction control Statement- SAVEPOINT, COMMIT oracle 12c TCL

Check the Result after rollback: Region Name changes from ‘Middle East’ to ‘Middle East and Africa’.

Transaction control Statement- SAVEPOINT, COMMIT oracle 12c TCL


Working with savepoints in Transaction:

Region Table before transaction

Check Countries in Region 4

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Check Countries in Region 5

No countries are there in Region 5.

Running a transaction with several savepoint.

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

UPDATE COUNTRIES
SET REGION_ID = 5
WHERE COUNTRY_ID = 'ZM';

SAVEPOINT zambia;

UPDATE COUNTRIES
SET REGION_ID = 5
WHERE COUNTRY_ID = 'NG';

SAVEPOINT nigeria;


UPDATE COUNTRIES
SET REGION_ID = 5
WHERE COUNTRY_ID = 'ZW';

SAVEPOINT zimbabwe;

UPDATE COUNTRIES
SET REGION_ID = 5
WHERE COUNTRY_ID = 'EG';

SAVEPOINT egypt;

Now Check REGIONS table after transaction:

Check the list of Countries in Region 4

Check the list of countries in Region 5

Now Rollback To Savepoint Nigeria

ROLLBACK TO SAVEPOINT nigeria;

Check REGIONS table after rollback:

Check list of countries in region 4 after rollback:

Check list of countries in region 5 after rollback:

Hence, we are done with the Transaction control Statement- SAVEPOINT, COMMIT and ROLLBACK and  in Oracle 12c.

Hope You enjoyed the tutorial.

Leave a Reply