Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

Day 28: Working with Oracle Temporary Table

Oct 11 2011 7:55AM by Niladri Biswas   

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

  1. Session based
  2. 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

Tags: #SQLServer, SQL Server, Oracle,


Niladri Biswas
7 · 21% · 6720
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Day 28: Working with Oracle Temporary Table" rated 5 out of 5 by 1 readers
Day 28: Working with Oracle Temporary Table , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]