Global Temporary Tables

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection.

These tables do not reside in the system catalogs and are not persistent. Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection. When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped.

Temporary tables are useful when:

• The table structure is not known before using an application.
• Other users do not need the same table structure.
• Data in the temporary table is needed while using the application.
• The table can be declared and dropped without holding the locks on the system catalog.

1. Create Global Temporary  Table and Insert Rows and Run Select Statement to See Values from the Table.

 --DROP TABLE SUPPLIERS_TEMP;

/*

CREATE GLOBAL TEMPORARY TABLE SUPPLIERS_TEMP

( SUPPLIER_ID NUMERIC(10) NOT NULL,

SUPPLIER_NAME VARCHAR2(50) NOT NULL,

CONTACT_NAME VARCHAR2(50)

) ON COMMIT DELETE ROWS; --ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

*/

CREATE GLOBAL TEMPORARY TABLE SUPPLIERS_TEMP

( SUPPLIER_ID NUMERIC(10) NOT NULL,

SUPPLIER_NAME VARCHAR2(50) NOT NULL,

CONTACT_NAME VARCHAR2(50)

) ON COMMIT PRESERVE ROWS; --ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

 BEGIN

INSERT INTO SUPPLIERS_TEMP VALUES (1, 'Dheeraz Thandlam', 'Son of Lokanadham Thandlam');

INSERT INTO SUPPLIERS_TEMP VALUES (2, 'Nishanth Thandlam', 'Son of Lokanadham Thandlam');

INSERT INTO SUPPLIERS_TEMP VALUES (3, 'Sasi Thandlam', 'Wife of Lokanadham Thandlam');

END;

SELECT * FROM SUPPLIERS_TEMP

2. Close the Current Session.

3. Open New Session with Same Schema

4. Run The select Statement again to check the rows. But No Rows Returned.