Thursday, February 8, 2018

What is Global temporary Tables in Oracle







/*****************************************************************************
 DECLARE GLOBAL TEMPORARY TABLE table-Name
    { column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ] 
NOT LOGGED [ON ROLLBACK DELETE ROWS]

 *****************************************************************************/
  CREATE GLOBAL TEMPORARY TABLE students 
   ( student_id numeric(10) NOT NULL, 
     student_name varchar2(50) NOT NULL, 
     student_address varchar2(50) 
    ); 
   
drop table GLOBAL_TEMP_TABLE ;   
 CREATE GLOBAL TEMPORARY TABLE GLOBAL_TEMP_TABLE
 ( id NUMBER,
 description VARCHAR2(20)
 ) ON COMMIT   DELETE ROWS;

insert into GLOBAL_TEMP_TABLE values(1,'this is first row');

select * from GLOBAL_TEMP_TABLE;

commit;
drop  table GLOBAL_TEMP PURGE;

CREATE GLOBAL TEMPORARY TABLE GLOBAL_TEMP
(
  id           NUMBER,
  description  VARCHAR2(50)
)ON COMMIT PRESERVE ROWS;

commit;
insert into global_temp values(2,'this is ON COMMIT PRESERVE ROWS');

select * from global_temp;

No comments:

Post a Comment