CREATE GLOBAL TEMPORARY TABLE temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS
NOCACHE;
ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.
CREATE GLOBAL TEMPORARY TABLE temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS
NOCACHE;
ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session.
Example:
CREATE GLOBAL TEMPORARY TABLE APPS.XXJG_ITEM_ONHAND_QTY
(
ORGANIZATION_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
ONHAND_QUANTITIES_ID NUMBER,
LOT_NUMBER VARCHAR2(40 BYTE),
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
PRIMARY_UOM_CODE VARCHAR2(10 BYTE),
PRIMARY_TRANSACTION_QUANTITY NUMBER(38,10),
PRIMARY_RESERVATION_QUANTITY NUMBER(38,10),
USABLE_QUANTITY NUMBER(38,10),
CREATION_DATE DATE
)
ON COMMIT PRESERVE ROWS
RESULT_CACHE (MODE DEFAULT)
NOCACHE;