What is the global temporary tables?

Global temporary tables are temporary table for particular session.
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.

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.

Syntax

DECLARE GLOBAL TEMPORARY TABLE table-Name
{ column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ]
NOT LOGGED [ON ROLLBACK DELETE ROWS]

table-Name

Names the temporary table. If a schema-Name other than SESSION is specified, an
error will occur (SQLSTATE 428EK). If the schema-Name is not specified, SESSION
is assigned. Multiple connections can define declared global temporary tables with the
same name because each connection has its own unique table descriptor for it.
Using SESSION as the schema name of a physical table will not cause an error, but is
discouraged. The SESSION schema name should be reserved for the temporary table

schema.

column-definition

See column-definition for CREATE TABLE for more information
on column-definition. DECLARE GLOBAL TEMPORARY TABLE does not

allow generated-column-spec in the column-definition.

Data type

Supported data types are:

1. BIGINT
2. CHAR
3. DATE
4. DECIMAL
5. DOUBLE
6. DOUBLE PRECISION
7. FLOAT
8. INTEGER
9. NUMERIC
10. REAL
11. SMALLINT
12. TIME
13. TIMESTAMP
14. VARCHAR

ON COMMIT

Specifies the action taken on the global temporary table when a COMMIT operation is

performed.

DELETE ROWS

All rows of the table will be deleted if no hold-able cursor is open on the table. This is

the default value for ON COMMIT. If you specify ON ROLLBACK DELETE

ROWS, this will delete all the rows in the table only if the temporary table was used.

ON COMMIT DELETE ROWS will delete the rows in the table even if the table was

not used (if the table does not have hold-able cursors open on it).

PRESERVE ROWS


The rows of the table will be preserved.

NOT LOGGED

Specifies the action taken on the global temporary table when a rollback operation is
performed. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is
performed, if the table was created in the unit of work (or savepoint), the table will be
dropped. If the table was dropped in the unit of work (or savepoint), the table will be
restored with no rows.

ON ROLLBACK DELETE ROWS

This is the default value for NOT LOGGED. NOT LOGGED [ON ROLLBACK
DELETE ROWS ]] specifies the action that is to be taken on the global temporary
table when a ROLLBACK or (ROLLBACK TO SAVEPOINT) operation is

performed. If the table data has been changed, all the rows will be deleted.


USES:

• 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.

Restrictions on Declared Global Temporary Tables

• Temporary tables cannot be specified in the following statements:

1. ALTER TABLE
2. CREATE INDEX
3. CREATE SYNONYM
4. CREATE TRIGGER
5. CREATE VIEW
6. GRANT
7. LOCK TABLE
8. RENAME
9. REVOKE

• You cannot use the following features with temporary tables.

1. Synonyms, triggers and views on SESSION schema tables (including physical tables and temporary tables)
2. Caching statements that reference SESSION schema tables and views
3. Temporary tables cannot be specified in referential constraints and primary keys
4. Temporary tables cannot be referenced in a triggered-SQL-statement
5. Check constraints on columns
6. Generated-column-spec
7. Importing into temporary tables

If a statement that performs an insert, update, or delete to the temporary table encounters an error, all the rows of the temporary table are deleted.

• The following data types cannot be used with Declared Global Temporary Tables.

1. BLOB
2. CHAR FOR BIT DATA
3. CLOB
4. LONG VARCHAR
5. LONG VARCHAR FOR BIT DATA
6. VARCHAR FOR BIT DATA
7. XML

Examples

set schema myapp;

create table t1(c11 int, c12 date);

declare global temporary table SESSION.t1(c11 int) not logged;

The SESSION qualification is redundant here because temporary

tables can only exist in the SESSION schema.

declare global temporary table t2(c21 int) not logged;

The temporary table is not qualified here with SESSION because temporary

tables can only exist in the SESSION schema.

insert into SESSION.t1 values (1);

SESSION qualification is mandatory here if you want to use

the temporary table, because the current schema is "myapp."

select * from t1;

This select statement is referencing the "myapp.t1" physical

table since the table was not qualified by SESSION.

Note: Temporary tables can be declared only in the SESSION schema. You should

never declare a physical schema with the SESSION name.

WHENEVER SQLERROR CONTINUE;

DROP TABLE xx.xx_xxincom_tax_gtt;

CREATE GLOBAL TEMPORARY TABLE xx.xx_xxincom_tax_gtt
( invoice_id                NUMBER
, invoice_num               VARCHAR2(100)
, invoice_type              VARCHAR2(100)
, invoice_date              DATE
, vendor_id                 NUMBER   
, vendor_name               VARCHAR2(240)
, vendor_taxid              VARCHAR2(50)
, taxable_amt               NUMBER
, tax_amt                   NUMBER
, invoice_status            VARCHAR2(100)
, tax_rep_status            VARCHAR2(100)
, cla_est_name              VARCHAR2(240)
, cla_inv_date              DATE
, cla_period                VARCHAR2(150)
, cla_sup_name              VARCHAR2(150)
, cla_sup_tax_id            VARCHAR2(150)
, cla_rec_date              VARCHAR2(150)
, cla_seq_no                VARCHAR2(150)
, cla_phor_type             VARCHAR2(150)
, cla_base_amt              NUMBER
, cla_tax_amt               NUMBER
, tax_inv_num               VARCHAR2(150)
, check_id                  NUMBER
, gl_date                   DATE
, check_taxable_amt         NUMBER
, check_tax_amt             NUMBER
, request_id                NUMBER
, creation_date             DATE
, last_update_date          DATE
, created_by                NUMBER
, last_updated_by           NUMBER
, last_update_login         NUMBER)
ON COMMIT PRESERVE ROWS;

COMMENT ON TABLE xx.xx_xxincom_tax_gtt IS 'Custom Global Temporary Table'; 
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.invoice_id          IS 'Invoice ID';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.invoice_num         IS 'Invoice Number';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.invoice_type        IS 'Invoice Type';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.invoice_date        IS 'Invoice Date';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.vendor_id           IS 'Vendor Id';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.vendor_name         IS 'Supplier Name';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.vendor_taxid        IS 'Supplier Tax Id';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.taxable_amt         IS 'Invoice Tax Base Amount';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.tax_amt             IS 'Invoice Tax Amount';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.invoice_status      IS 'Invoice/Payment Document Status';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.tax_rep_status      IS 'Tax Reporting Status';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_est_name        IS 'Tax Invoice - Establishment Name';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_inv_date        IS 'Tax Invoice - Invoice Date';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_period          IS 'Tax Invoice - Accounting Period';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_sup_name        IS 'Tax Invoice - Actual Supplier Name';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_sup_tax_id      IS 'Tax Invoice - Actual Supplier Tax ID';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_rec_date        IS 'Tax Invoice - Received Date';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_seq_no          IS 'Tax Invoice - Sequence Number';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_phor_type       IS 'Tax Invoice - Phor Phor Type';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_base_amt        IS 'Tax Invoice - Base Amount';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.cla_tax_amt         IS 'Tax Invoice - Tax Amount';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.tax_inv_num         IS 'Tax Invoice - Invoice Number';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.check_id            IS 'Check ID'; 
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.gl_date             IS 'GL Date of the AP Invoice/Payment Header';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.check_taxable_amt   IS 'Taxable Amount for Check Id'; 
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.check_tax_amt       IS 'Tax Amount for Check Id'; 
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.request_id          IS 'Concurrent Program Request Id';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.creation_date       IS 'WHO Column';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.last_update_date    IS 'WHO Column';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.last_updated_by     IS 'WHO Column';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.created_by          IS 'WHO Column';
COMMENT ON COLUMN xx.xx_xxincom_tax_gtt.last_update_login   IS 'WHO Column';

GRANT ALL    ON xx.xx_xxincom_tax_gtt TO APPS;
GRANT SELECT ON xx.xx_xxincom_tax_gtt TO TL1;
SHOW ERRORS;