How to store data temporarily for later use in Oracle?

OracleSoftware & CodingProgramming

Problem:

You want to store the results of a SQL temporarily.

Solution

We can use the CREATE GLOBAL TEMPORARY TABLE statement to create a table that stores data temporarily for a session. Further, you can specify whether to retain temporary table data for a session or until a transaction commits. We can further Use ON COMMIT PRESERVE ROWS clause to specify the data be deleted at the end of the user’s session.

Example

CREATE GLOBAL TEMPORARY TABLE tmp_players ON COMMIT PRESERVE ROWS AS SELECT * FROM players WHERE coach_id IS NOT NULL;

Global temporary tables store session private data that exists only for the duration of the session. Onceweyou create a temporary table, it exists until we drop it.

Example

select table_name, temporary from user_tables WHERE temporary = 'Y'; TMP_PLAYERS Y

When we can create records within a temporary table, space is allocated in our default temporary tablespace. We can verify this by running the following SQL.

Example

SELECT username, contents, segtype FROM v$sort_usage;

To improve the performance on the table, we can create the index and drop them at the end of the session.

Finally, we can use ON COMMIT DELETE ROWS clause to indicate the data should be deleted at the end of the session. The following example explains the same.

Example

CREATE GLOBAL TEMPORARY TABLE tmp_players ON COMMIT DELETE ROWS AS SELECT * FROM players WHERE coach_id IS NOT NULL;

Data Preparation: Data used for the problem is shown below.

Example

DROP TABLE players;
COMMIT;

CREATE TABLE players
    ( player_rank    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) CONSTRAINT player_last_name_nn  NOT NULL
    , email          VARCHAR2(30) CONSTRAINT player_email_nn      NOT NULL
    , phone_number   VARCHAR2(20)
      , player_start_date      DATE CONSTRAINT     player_start_date_nn  NOT NULL
    , title_id         VARCHAR2(20) CONSTRAINT     player_title  NOT NULL
    , prize_money    NUMBER(8,2)
    , coach_id       NUMBER(6)
    , CONSTRAINT     player_email   UNIQUE (email)
    ) ;

Example

INSERT INTO players VALUES (1,'Roger','Federer','roger.federer@notreal.com','111.111.1234',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'AUS_OPEN','350000',1);
INSERT INTO players VALUES (2,'Rafa','Nadal','Rafa.Nadal@notreal.com','111.111.1235',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'FRENCH_OPEN','300000',1);
INSERT INTO players VALUES (3,'Andy','Murray','Andy.Murray@notreal.com','111.111.1236',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'WIMBELDON','450000',1);
INSERT INTO players VALUES (4,'Stan','Wawrinka','Stan.Wawrinka@notreal.com','111.111.1237',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'FRENCH_OPEN','300000',2);
INSERT INTO players VALUES (5,'Dominic','Theim','Dominic.Theim@notreal.com','111.111.1238',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'US_OPEN','500000',2);
INSERT INTO players VALUES (6,'Novak','Djokovic','Novak.Djokovic@notreal.com','111.111.1239',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'AUS_OPEN','350000',2);
INSERT INTO players VALUES (7,'Andy','Zverev','Andy.Zverev@notreal.com','111.111.1240',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'FRENCH_OPEN','300000',2);
INSERT INTO players VALUES (8,'Andy','Rublev','Andy.Rublev@notreal.com','111.111.1241',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'WIMBELDON','450000',3);
INSERT INTO players VALUES (9,'Janik','Sinner','Janik.Sinner@notreal.com','111.111.1242',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'FRENCH_OPEN','300000',3);
INSERT INTO players VALUES (10,'Danil','Medvedev','Danil.Medvedev@notreal.com','111.111.1243',TO_DATE('17-06-2003', 'dd-MM-yyyy'),'US_OPEN','500000',3);

COMMIT;
raja
Published on 05-Dec-2020 06:18:15
Advertisements