Transactions

A transaction in Oracle is like a personal workspace for a period of time – no other user can see your DML changes to the database until you COMMIT them to the database, you can also ROLLBACK any changes you’ve made or if an error occurs. Both of these statements essentially close of one transaction and start another session.

Syntax

COMMIT;
ROLLBACK;

Example

BEGIN
INSERT INTO employee ( employee_id,
first_name,
surname,
date_of_birth,
manager_ind )
VALUES ( 1234,
‘Jim’,
‘Bloggs’,
’02-MAY-89’,
‘N’ );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;

The syntax for these commands are as simple as they appear – the above example is inserting a single record into the employee table, if it completes the insert successfully then it commits the change to the database. However, if the insert should fail for any reason the code will drop into the exception handler and rollback the transaction.

There are more advanced uses within the transaction space such as SAVEPOIINT and PRAGMA AUTONOMOUS_TRANSACTION – these allow a user to rollback to specific point in the code to avoid rollback all the work and when calling a procedure within a procedure AUTONOMOUS_TRANSACTION will allow you to commit that work in a separate transaction without affecting your ability to rollback or commit the main transaction. More information can be obtained by signing up to our DSaaS service.

Leave a Reply

Your email address will not be published. Required fields are marked *