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.
/*
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.
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.
