Let Us Learn Oracle - Part 28 of N [ How to create and use Temporary table in Oracle ]
Oracles temporary tables are statically defined.They are created once per database and not for individual stored procedures in the database.It is created in the user temporary table space.
Temporary tables in Oracle can be
- Session based
- Transaction based
1. Transaction based
Create Global Temporary table temp_table
(
Col1 DataType
,Col2 DataType
)
On Commit Delete Rows;
On Commit Delete Rows indicates that data should be deleted at the end of the transaction
2. Session based
Create Global Temporary table temp_table
(
Col1 DataType
,Col2 DataType
)
On Commit Preserve Rows;
On Commit Preserve Rows clause indicates that rows should be preserved till the session ends.
Experiment on Session based Temporary Table
Create the table structure first
Create Global Temporary Table tblTemp
On Commit Preserve Rows
As
Select *
From tblPlayers
Where 1=0;
We can make sure that the table srtructure has been created properly
SQL> Desc tblTemp;
Name Null? Type
----------------------------------------- -------- ----------------------------
PLAYERID NOT NULL NUMBER(38)
PLAYERFIRSTNAME VARCHAR2(10)
PLAYERLASTNAME VARCHAR2(10)
BELONGSTO VARCHAR2(10)
DOB DATE
FEEPERMATCH NUMBER(7,2)
We can even see that it has been created in Owner Scott
Select *
From All_Tables
Where Owner = 'SCOTT'
And Table_Name = 'TBLTEMP';
Let us insert some record
Insert Into tblTemp
Select *
From tblPlayers;
Commit;
SQL> Select *
2 From tblTemp;
PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
11 P Chopra India 14-FEB-98
1 A Raman India 20-DEC-84 2000
2 B Kadir India 01-JAN-80 1000
3 C Nadir India 21-APR-00 3000
4 D Das India 11-NOV-80 500
5 E Punchkar India 14-FEB-80 12000
6 F Fateman USA 20-DEC-71 22000
7 G Gajani USA 01-JAN-90 999
8 H Hariharan USA 21-APR-99 7000
9 I Ikat USA 11-NOV-88 5999
10 J Jwar USA 14-FEB-98 7900
11 rows selected.
Now we will connect again and thereby a new session will be started
SQL> Connect scott/tiger@orcl;
Connected.
So a new session has been created
Now let us query the temptable
SQL> Select *
2 From tblTemp;
no rows selected
Experiment on Transaction based Temporary Table
Create the table structure first
Create Global Temporary Table tblTemp_Tran
On Commit Delete Rows
As
Select *
From tblPlayers
Where 1=0;
Let us insert some record
Insert Into tblTemp_Tran
Select *
From tblPlayers;
SQL> Select *
2 From tblTemp_Tran;
PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
11 P Chopra India 14-FEB-98
1 A Raman India 20-DEC-84 2000
2 B Kadir India 01-JAN-80 1000
3 C Nadir India 21-APR-00 3000
4 D Das India 11-NOV-80 500
5 E Punchkar India 14-FEB-80 12000
6 F Fateman USA 20-DEC-71 22000
7 G Gajani USA 01-JAN-90 999
8 H Hariharan USA 21-APR-99 7000
9 I Ikat USA 11-NOV-88 5999
10 J Jwar USA 14-FEB-98 7900
11 rows selected.
At this stage we can find that our temporary table has all the records of the tblPlayers.
Now let us commit and see the behaviour
SQL> Commit;
Commit complete.
SQL> Select *
2 From tblTemp_Tran;
no rows selected
Which indicates that the records gets deleted once we committed
And similarly if we start a new session, then there won't be any value in the temporary table
Hope you like this.Thanks for reading