Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to store data temporarily for later use in Oracle?
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;
1K+ Views
