Skip to main content

Temporary Tables

 Temporary tables

Temporary tables data can be for a temporary period of a transaction or a session.
Temporary data for a transaction example :-


create global temporary table emp_temp
(empno number,
 ename varchar2(30),
 sal number,
 deptno number) on commit delete rows;

  

Insert records to test data for a transaction:-

insert into emp_temp values ( 1, 'krishna', 1000, 10);
insert into emp_temp values ( 2, 'Raghu', 1000, 10);
insert into emp_temp values ( 3, 'Sachin', 1000, 10);
insert into emp_temp values ( 4, 'Amit', 1000, 10);


Verifying table data before deleting with a commit command :-

select * from emp_temp;

Issuing commit to delete the records of the temporary table:-

commit;

Verifying table data after deleting with a commit command :-

select * from emp_temp;

no rows selected



Temporary data for a session example :-

Drop table emp_temp;

create global temporary table emp_temp
(empno number,
 ename varchar2(30),
 sal number,
 deptno number) on commit preserve rows;

  

Insert records to test data for a transaction:-


insert into emp_temp values ( 1, 'krishna', 1000, 10);
insert into emp_temp values ( 2, 'Raghu', 1000, 10);
insert into emp_temp values ( 3, 'Sachin', 1000, 10);
insert into emp_temp values ( 4, 'Amit', 1000, 10);


Verifying table data that should delete after ending a session :-

select * from emp_temp;


Issuing disc command  to end your session and delete the records of the temporary table:-

disc;

Connect back to check if the records are available:-

select * from emp_temp;

no rows selected


Comments

Sahaja said…
Step by step process is mentioned which is very helpful for beginners.
Malavieka said…
Each step is explained very well.
Thank you.
Well explained
thank you sir
Samreen khan said…
Very well explained
Thankyou Sir
This comment has been removed by the author.
AMISHA GUPTA said…
Clear explanation for beginners.
Thank you
sindhuja said…
each step was explained clearly and very useful for beginners
Rinitha Jose said…
Very helpful. Easy to understand with the example provided.
Anonymous said…
Easy to learn for beginners ..Gud Explanation sir
Thank you.
Unknown said…
Very helpful Content for beginners.
Thank you sir....
bhavya said…
Well explained sir
thankyou
Very nicely explained. Thankyou for sharing!
bhavana said…
good and easy explanation
Well explained! Thankyou sir
Shantha said…
Very well explained and easy to understand. Thank you.
Manasa said…
Easy to understand and very helpful for beginners
sayani said…
The topic is explained clearly step by step. thank you, sir.
Unknown said…
Explained very well sir!
Tanya said…
Nicely explained. Thank you sir!
Unknown said…
Very helpful content for beginners.
Thank you sir....
Very well explained.
Thank you
Pranav said…
easy to understand nicely explained

Popular posts from this blog

Oracle Merge Statement Delta Detection

The MERGE Syntax by Ajay Nerumati Delta Detection in Oracle SQL Posted on  8. October 2016 Delta detection is a common task in every Data Warehouse. It compares new data from a source system with the last versions in the Data Warehouse to find out whether a new version has to be created. There are several ways to implement this in Oracle. Your source system delivers a full extraction every night, and you have to load only the changed rows into your Core Data Warehouse? You receive incremental loads from another source system every few minutes, but only a few columns are loaded into the Data Warehouse. In all these situations, you need a delta detection mechanism to identify the rows that have to be inserted or updated in your Data Warehouse tables. In this blog post, I want to show different methods in Oracle SQL that provide the subset of rows of a source table that were changed since the last load. All these methods are set-based, i.e. they can be executed in one SQL sta...

FOREIGN KEY

Pre-requisits : General understanding of integrity constraints. Constrains are used to prevent invalid data into the table. Integrity constrains are business rules enforced on the data in a table. The enforced rules trigger whenever a row is inserted, updated, or deleted from that table and prevent data that does not meet the set rules. FOREIGN KEY :  Establishes and enforces a relationship between two tables or to the same table itself. The foreign key or referential integrity constraint, designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.  In the example below   Emp_child  table's deptno   has been defined as the foreign key ; it references the deptno column of the Dept_parent table. Implementation: Step 1 Create parent table DEPT_PARENT as follows: Create table DEPT_PARENT ( Deptno n...

RDBMS MINI PROJECT

  Capgemini class room training.   RDBMS MINI PROJECT ( SPRINT ) LIBRARY MANAGEMENT SYSTEM   Table of Contents Serial No. Topic Name Content Page No. 1.   Introduction 1.1 Setup checklist for mini project 3     1.2 Instructions 3 2.   Problem statement   2.1 Objective 4     2.2 Abstract of the project 4     2.3 Functional components of the project 4     2.4 Technology used 5 3.   Implementation in RDBMS LOT 3.1 Guidelines on the functionality to be built 6 4.   Evaluation 4.1 Evaluation 7   ...