How to Use Global Temporary Table in Oracle Procedure?

In Oracle, global temporary tables known as session tables and we use global temporary tables to keep the data temporarily in it while processing. The below is an example.

Create a Global Temporary Table

Here we will create a global temporary table to hold department wise total salary from the EMP table. You can download the EMP table and the data for the testing purpose from the following link SCOTT Schema Tables. Also, in the below example, we are creating the table with ON COMMIT DELETE ROWS clause, to delete the rows whenever a Commit statement executed in the procedure. You can also use the ON COMMIT PRESERVE ROWS clause to preserve the rows into the table until the session is active.

CREATE GLOBAL TEMPORARY TABLE temp_dept
(
deptno NUMBER (4),
dname VARCHAR2 (50),
sal NUMBER
)
ON COMMIT DELETE ROWS;

Example to Use Global Temporary Table in Oracle Procedure

The following procedure will take the total salary from each department and will populate the temp_dept table. Then will pick the records from a temp_dept table and will update the EMP table commission column with 2% of total department salary.

CREATE OR REPLACE PROCEDURE prc_dept
IS
CURSOR c_emp
IS
SELECT e.deptno, d.dname, SUM (e.sal) tot_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY e.deptno, d.dname;

n_count NUMBER := 0;
BEGIN
FOR c IN c_emp
LOOP
/* Inserting records into temp table */
INSERT INTO temp_dept (deptno, dname, sal)
VALUES (c.deptno, c.dname, c.tot_sal);
END LOOP;

/* Now get the records from temp table and update the EMP table */
FOR c IN (SELECT deptno, dname, sal FROM temp_dept)
LOOP
/* Updating the EMP table commission column to set 2% of total department wise salary*/
UPDATE emp
SET comm = c.sal * 2 / 100
WHERE emp.deptno = c.deptno;

DBMS_OUTPUT.put_line( 'Commission amount '
|| (c.sal * 2 / 100)
|| ' updated for department '
|| c.dname);
END LOOP;

/* Save the EMP table changes and this will also remove the records from temp_dept table*/
COMMIT;

/* Checking temporary table records count for testing */
SELECT COUNT ( * ) INTO n_count FROM temp_dept;

DBMS_OUTPUT.put_Line ('Records in Temp table: ' || n_count);
END;

Test

SET SERVEROUTPUT ON;

BEGIN
prc_dept;
END;
/

Output

Commission amount 175 updated for department ACCOUNTING
Commission amount 217.5 updated for department RESEARCH
Commission amount 188 updated for department SALES
Records in Temp table: 0
PL/SQL procedure successfully completed.

See also:

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.