Create global temporary table working_emps on commit preserve rows : Temporary Table « Table « Oracle PL / SQL






Create global temporary table working_emps on commit preserve rows

    
  
SQL>
SQL> create table emp(
  2           emp_id                integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));

Table created.

SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (1,'Jones','Joe','J','1 Ave','New York','NY','11202','1111','212', '221-4333','Big Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (2,'Smith','Sue','J','1 Street','New York','NY','11444','1111','212', '436-6773','Little Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (3,'X','Peggy','J','1 Drive','New York','NY','45502','2222','212', '234-4444','Medium Company');

1 row created.

SQL>
SQL>
SQL> create global temporary table working_emps
  2  on commit preserve rows
  3  as select * from emp;


SQL>
SQL> select *
  2  from working_emps;

    EMP_ID LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   SHOR ARE PHONE    COMPANY_NAME
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- --------------------------------------------------
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company

12 rows selected.

SQL>
SQL> select * from working_emps;

    EMP_ID LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   SHOR ARE PHONE    COMPANY_NAME
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- --------------------------------------------------
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company
         1 Jones                Joe             J 1 Ave                          New York             NY 11202 1111 212 221-4333 Big Company
         2 Smith                Sue             J 1 Street                       New York             NY 11444 1111 212 436-6773 Little Company
         3 X                    Peggy           J 1 Drive                        New York             NY 45502 2222 212 234-4444 Medium Company

12 rows selected.

SQL>
SQL> insert into working_emps
  2  select * from emp ;

3 rows created.

SQL>
SQL> drop table working_emps;
drop table working_emps
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


SQL> drop table emp;

Table dropped.

SQL>

   
    
    
    
  








Related examples in the same category

1.Create temporary table
2.Create global temporary table from existing table
3.Create global temporary table with 'on commit delete rows' option
4.Temporary tables cannot be forced into logging.
5.Temporary tables support primary keys.
6.Temporary tables do not support foreign keys
7.You cannot alter a temporary table to change its data duration.(drop and recreate)
8.ORA-14452: attempt to create, alter or drop an index on temporary table already in use
9.Update a TEMPORARY TABLE and check the table it based on
10.create global temporary table on commit delete rows
11.Temporary table on commit preserce and delete
12.drop global temporary table
13.Insert into a temporary with select statement
14.Insert data into the temporary table.
15.truncate a global temporary table
16.Using Temporary Tables