How to Create Global Temporary Tables


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;