Global Temporary Table (GTT) concepts in PL/SQL

      Oracle allows to create the Temporary table like permanent table is called Global Temporary Table (GTT). GTT is used to store or manage the data temporarily which is not required for reference in the future.

Types of Global Temporary Table:
There are 2 Types of GTT

1)Session Specific GTT
For session specific GTT, Data exists for the duration of specific session.

2)Transaction Specific GTT
For transaction specific GTT data exists for the duration of the transaction.

Features of GTT:
• The Data in the GTT is private to the particular session.
• Each session can only see and modify its own data. It cannot see or read other sessions data.
• As each session has its private data, The LOCK Statement has no effect on GTT.
• The TRUNCATE statement used in the session specific GTT truncates only the data which is present in the particular session. It will not effect on he data in another session for the same GTT.
• You can create INDEX on GTT and it is also a temporary.
• You can create TRIGGERS on GTT.
• You can create a view which has both Temporary and Permanent tables.

Syntax:
CREATE GLOBAL TEMPORARY TABLE <table_name> (COL1 DEFINTION [,COL2 DEFINTION...] ) ON COMMIT [DELETE | PRESERVE] ROWS;

Note that in the syntax there are 2 options to perform ON COMMIT.
1) If you use ON COMMIT DELETE ROWS the data will be deleted from the GTT once commit statement is executed in the session. i.e. it is a transaction specific GTT.
2) If you use ON COMMIT PRESERVE ROWS the data in the GTT remains till the end of the specific session. i.e. it is session specific GTT.

Lets’ discuss with an example:
1)Create a Transaction Specific GTT
CREATE GLOBAL TEMPORARY TABLE GTT_TEST_TRANS_TAB (id NUMBER, name VARCHAr2(50)) ON COMMIT DELETE ROWS;

2) Create a PL/SQL block to verify the Transaction Specific GTT
DECLARE l_count NUMBER; l_query VARCHAR2(100); BEGIN l_query :='INSERT INTO GTT_TEST_TRANS_TAB VALUES(1,''RG'')'; EXECUTE IMMEDIATE(l_query); SELECT COUNT(*) INTO l_count FROM GTT_TEST_TRANS_TAB ; DBMS_OUTPUT.PUT_LINE('The record count in GTT before commit :'||l_count); COMMIT; SELECT COUNT(*) INTO l_count FROM GTT_TEST_TRANS_TAB ; DBMS_OUTPUT.PUT_LINE('The record count in GTT AFTER commit :'||l_count); END;

3)The output of above block as below

   You can see that before the commit statement execute the count in the table was 1 but once the commit statement is executed the data in the GTT has been deleted and the count become 0.

4) Create a Session Specific GTT
CREATE GLOBAL TEMPORARY TABLE GTT_TEST_SESS_TAB (id NUMBER, name VARCHAr2(50)) ON COMMIT PRESERVE ROWS;

5)Create a PL/SQL block to verify the Session Specific GTT
DECLARE l_count NUMBER; l_query VARCHAR2(100); BEGIN l_query :='INSERT INTO GTT_TEST_SESS_TAB VALUES(1,''RG'')'; EXECUTE IMMEDIATE(l_query); SELECT COUNT(*) INTO l_count FROM GTT_TEST_SESS_TAB ; DBMS_OUTPUT.PUT_LINE('The record count in GTT before commit :'||l_count); COMMIT; SELECT COUNT(*) INTO l_count FROM GTT_TEST_SESS_TAB ; DBMS_OUTPUT.PUT_LINE('The record count in GTT AFTER commit :'||l_count); END;

6) The output of above block as below

      You can see that before the commit statement execute the count in the table was 1 but once the commit statement is executed the data in the GTT has preserved and the count is 1. There is no changes at transaction level. Once the session is completed the data in the GTT will be deleted.