🌐 Oracle SQL: GTT vs PTT (Temporary Tables)

🧪 Oracle SQL: Global vs Private Temporary Tables with Examples

#OracleSQL #TemporaryTable #GTT #PTT #SQLLab #SQLBanglaExample #SQLBloggerTutorial

Oracle SQL provides two types of temporary tables: Global Temporary Tables (GTT) and Private Temporary Tables (PTT). These are used to store session-specific or transaction-specific data temporarily.

🌐 1. Global Temporary Table (GTT)

• Structure is shared; data is private to each session or transaction
• Data is automatically cleared after session or transaction ends
• Useful for reusable processing in procedures/functions

🔧 Syntax:

CREATE GLOBAL TEMPORARY TABLE TEMP_BONUS (
  EMP_ID     NUMBER,
  EMP_NAME   VARCHAR2(50),
  BONUS      NUMBER
)
ON COMMIT PRESERVE ROWS;

🧪 Example:

-- Insert bonus data for Bangladeshi employees
INSERT INTO TEMP_BONUS (EMP_ID, EMP_NAME, BONUS)
VALUES (201, 'Rafiq Ahmed', 5000);

INSERT INTO TEMP_BONUS (EMP_ID, EMP_NAME, BONUS)
VALUES (202, 'Nasima Khatun', 4500);

✅ Use Case:

Temporarily store and calculate bonus information per employee during reports or processing, without saving to permanent tables.

---

🔒 2. Private Temporary Table (PTT)

• Introduced in Oracle 18c
• Table and data are both visible only in the current session
• Table name must start with ORA$PTT_
• Dropped automatically when session ends

🔧 Syntax:

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_TEMP_DEPT (
  DEPT_ID   NUMBER,
  DEPT_NAME VARCHAR2(50),
  TEMP_HEAD VARCHAR2(50)
);

🧪 Example:

-- Insert department head info for one-time use
INSERT INTO ORA$PTT_TEMP_DEPT (DEPT_ID, DEPT_NAME, TEMP_HEAD)
VALUES (10, 'IT', 'Hasan Mahmud');

INSERT INTO ORA$PTT_TEMP_DEPT (DEPT_ID, DEPT_NAME, TEMP_HEAD)
VALUES (20, 'Accounts', 'Rubina Sultana');

✅ Use Case:

For dynamic reports where department heads are calculated on-the-fly and discarded after the session ends.

---

🔍 3. Key Differences

Feature	Global Temporary Table	Private Temporary Table
Scope	Shared structure, private data	Private structure and data
Lifetime	Exists permanently	Dropped after session ends
Name format	Any valid name	Must start with ORA$PTT_
Oracle Version	Oracle 8i+	Oracle 18c+

---

🧪 4. Practice Suggestions

• Create a GTT to calculate session-based allowances for employees
• Create a PTT for department meetings temporary data
• Use SELECT statements to test visibility across sessions
• Test ON COMMIT DELETE vs PRESERVE in GTT

---

📘 Temporary tables are powerful for handling session-specific data. Use GTTs for reusable operations, and PTTs for short, isolated logic.