Oracle SQL Commands: Part 8

TCC Commands

1. COMMIT Command

The COMMIT Statement is used when we made some changes in the current transaction and want them to be permanent. In other words, to apply our changes permanently we use the COMMIT clause. Until the changes are committed we cannot access the modified data.

Syntax

COMMIT [WORK] [COMMENT 'your comment']        
                     
Or

INSERT INTO table_name VALUES (Val1,Val2,.....Valn);
COMMIT;

Example

Assume the following Employee table:

Emp_id

Emp_Name

City

110011

ASD

Delhi

110022

GHJ

Jodhpur

110033

RST

Patiala

110055

ABC

Haridwar

INSERT INTO Employees VALUES (11044, 'Rahul','Jaipur')
COMMIT;

Result

Emp_id

Emp_Name

City

110011

ASD

Delhi

110022

GHJ

Jodhpur

110033

RST

Patiala

110044

Rahul

Jaipur

110055

ABC

Haridwar

2. ROLLBACK Command

In Oracle, in a current transaction, if we made the changes and also commited them, then ROLLBACK is the only command that is used to undo the changes done by us. To undo/rollback your transaction, no privileges are necessary.

Syntax

ROLLBACK [WORK] TO <SAVEPOINT>] identifier;
                             
Or

INSERT INTO table_name VALUES (Val1,Val2,.....Valn);
ROLLBACK;
 
Example

Assume the following Organization table:

Org_id

Org_Name

City

123

TCS

Delhi

124

HCL

Noida

126

INFOSYS

Mumbai

128

WIPRO

Gurgoan

Query

INSERT INTO Organization VALUES (125, 'TECH. MAHINDRA','Noida');

INSERT INTO Organization VALUES (127, 'COMPRO PVT.LTD.','Delhi');

INSERT INTO Organization VALUES (129, 'NEIT','Mumbai');

COMMIT;
 
Result
 

Org_id

Org_Name

City

123

TCS

Delhi

124

HCL

Noida

126

INFOSYS

Mumbai

128

WIPRO

Gurgoan

125

TECH. MAHINDRA

Noida

127

COMPRO PVT. LTD.

Delhi

129

NEIT

Mumbai


Note: Here we inserted 3 rows but we need to insert only 2 rows so now use rollback and the result is:
 
Query
 

INSERT INTO Organization VALUES (125, 'TECH. MAHINDRA','Noida');

INSERT INTO Organization VALUES (127, 'COMPRO PVT.LTD.','Delhi');

INSERT INTO Organization VALUES (129, 'NEIT','Mumbai');

COMMIT;

ROLLBACK;


Result
 

Org_id

Org_Name

City

123

TCS

Delhi

124

HCL

Noida

126

INFOSYS

Mumbai

128

WIPRO

Gurgoan


3. SAVEPOINT Command

SAVEPOINT is used when we don't want to ROLLBACK all the changes during the current transaction, in other words we created savepoints during the changes made so that we can go to the specific SAVEPOINT to ROLLBACK the change.

Syntax

SAVEPOINT <Savepoint_Name>;
                             
Example

Assume the following Order table:

 

Order_id

Item_Name

Quantity

1

Shampoo

12

2

Hair Oil

8

5

Talc

5

9

Deo Spray

7


Query
 

INSERT INTO Order VALUES (7, Soap', 10);

SAVEPOINT a;

DELETE FROM Order VALUES (5, 'Talc',5);

SAVEPOINT b;

INSERT INTO Order VALUES (3, 'NEIT', 15);

COMMIT;
 
Result
 

Order_id

Item_Name

Quantity

1

Shampoo

12

2

Hair Oil

8

9

Deo Spray

7

7

Soap

10

3

Detergent

15

Note:  Here, in the preceding query we inserted 2 rows and deleted 1 row from the table order and also created SAVEPOINTS, but we inserted two rows instead of one. So, to rollback the insertion of the second row we rollback the transaction using the savepoint with rollback in the following way:

Query

INSERT INTO Order VALUES (7, Soap', 10);

SAVEPOINT a;

DELETE FROM Order VALUES (5, 'Talc',5);

SAVEPOINT b;

INSERT INTO Order VALUES (3, 'NEIT', 15);

ROLLBACK TO SAVEPOINT b;

COMMIT;
 
Result

Order_id

Item_Name

Quantity

1

Shampoo

12

2

Hair Oil

8

9

Deo Spray

7

7

Soap

10

4. SET TRANSACTION Command
 
As the name indicates, a SET TRANSACTION is a set of one or more transactions/statements that are executed together as a unit so that either all will be executed or none of them will be executed. It is used to establish the current transaction as read only or read write.

Syntax
 

SET TRANSACTION

{

{ READ { ONLY | WRITE }

| ISOLATION LEVEL

{ SERIALIZABLE | READ COMMITTED }

| USE ROLLBACK SEGMENT rollback_segment

}

[ NAME 'text' ]

| NAME 'text'

};

Since I have described the transaction levels in my article "Transactions in Oracle database", here I am sharing the Read only level in the following example.

Example

To set a transaction to read only you need to use the SET TRANSACTION READ ONLY Command. In DML the transactions will start automatically, thats why a SET TRANSACTION statement is issued before the DML data. Here the default scott schema is used:
 
SQL> connect HR/fyicenter
 
SQL> SET TRANSACTION READ ONLY;
Transaction set.
 
SQL> SELECT * FROM fyi_links;

     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06
    112 oracle.com                                  07-MAY-06
    113 sql.com                                       07-MAY-06

Keep the "HR" SQL*Plus window as-is and open another window to run another instance of SQL*Plus.

>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
 
SQL> connect SYSTEM/password
Connected.
 
SQL>  DELETE FROM hr.fyi_links where id = 112;
1 row deleted.
 
SQL>  DELETE FROM hr.fyi_links where id = 113;
1 row deleted.
 
SQL> COMMIT;

Commit complete.

Go back to the "HR" SQL*Plus window.

SQL> SELECT * FROM fyi_links;

     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06
    112 oracle.com                                  07-MAY-06
    113 sql.com                                       07-MAY-06
 
SQL> COMMIT;
Commit complete.
 
SQL> SELECT * FROM fyi_links;

     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06

As you can see, two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.

You can also read this from here: http://dba.fyicenter.com/faq/oracle/Set-READ-ONLY-Transaction.html

Previous article: Oracle SQL Commands: Part 7
Next Article: Oracle SQL Commands: Part 9