Create and delete temporary tables in Oracle

Create and delete temporary tables in Oracle

Create global temporary table tablename (
COL1 VARCHAR2 (10 ),
COL2 NUMBER
) On commit preserve (DELETE) ROWS;

-- On commit delete rows indicates that the temporary table is specified by a transaction. After each COMMIT, Oracle truncates the table (DELETE all ROWS)
---------------------------------------
In Oracle8i, you can create two types of temporary tables:
1. Session-specific temporary table
Create global temporary <TABLE_NAME> (<column specification>)
On commit preserve rows;

2. Temporary tables specific to transactions
Create global temporary <TABLE_NAME> (<column specification>)
On commit delete rows;
Create global temporary table MyTempTable
Although the created temporary table exists, you can try to insert a record and then mount it to select using other connections. The record is empty.
The following two sentences will be added:
-- On commit delete rows: What is next to COMMIT? The table is specified by a transaction. After each commit, ORACLE truncates the table (delete all rows)
-- On commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, ORACLE truncates the table.

Procedure executes a series of operations
A package can define some quantities, functions, and processes;

DDL statements cannot be directly used in Stored Procedures, so dynamic SQL statements can only be used for execution.

Create procedure pro
As
Str varchar2 (100 );
Begin

Str: = 'Create global temporary table admin_work_area
(Startdate DATE,
Enddate DATE,
Class CHAR (20 ))
On commit delete rows ';
Execute immediate str;
End;

Delete:

Truncate table MyTempTable
Drop table MyTempTable

ORA-22992: unable to use the LOB locator selected from the remote table

Solution:
You can create a temporary table first, then clone the table with the remote LOB field to the temporary table, and then perform the link operation.
1. Create a temporary table locally
SQL code
 
Create global temporary table photo_temp as select * from photo @ photo_link where 1 = 2;

2. Use database link to import remote data to a temporary table
SQL code
 
Insert into photo_temp select * from photo @ photo_link; -- do not commit; otherwise, data in the temporary table disappears.

3. Insert the temporary table data to the Permanent Table:
SQL code
Insert into photo select * from photo_temp;
Commit;

Instance:

Create global temporary table pic_temp as select * from WH_REGISTERPIC @ ogdpshdb where 1 = 2;
Insert into pic_temp select * from WH_REGISTERPIC @ ogdpshdb;
Insert into WH_REGISTERPIC select * from pic_temp;
Commit;

Delete temporary table:

Truncate table pic_temp;
Drop table pic_temp;

Solve the problem that normal users do not have permission to create temporary tables during the stored procedure:

Add AUTHID CURRENT_USER to the header

Example:

Create or replace package WH_Info_Output AUTHID CURRENT_USER is

-- Author:
-- Created: September 2, 2014 15:27:25
-- Purpose:

-- Delete original data and import all data
Procedure SP_WH_Info_Output_All (Fid in number default 1 );
-- Public type declarations
-- Input parameter 0 by default: import data not available 1: delete original data import all data
Procedure SP_WH_Info_Output (Fid in number default 0 );

-- Import the WH_REGISTERPIC table (containing the BLOG field) 0: import data that does not exist 1: delete original data import all data
Procedure SP_WH_PIC_BLOGInfo_Output (Fid in number default 0 );

End WH_Info_Output;

 

-- Import the WH_REGISTERPIC table (containing the BLOG field) 0: import data that does not exist 1: delete original data import all data
Procedure SP_WH_PIC_BLOGInfo_Output (Fid in number default 0)
Is
Str varchar2 (300 );
Begin
If Fid = 0 then
Str: = 'Create global temporary table pic_temp as select * from WH_REGISTERPIC @ ogdpshdb where 1 = 2 ';
Execute immediate str;
Str: = 'insert into pic_temp select * from WH_REGISTERPIC @ ogdpshdb w where (w. WELLID, w. PICTYPECODE, w. VERSIONNO, w. PICFILENAME) not in (select WELLID, PICTYPECODE, VERSIONNO, PICFILENAME from WH_REGISTERPIC )';
Execute immediate str; ---- use dynamic SQL statements to execute
Str: = 'insert into WH_REGISTERPIC select * from pic_temp ';
Execute immediate str;
End if;
 

End SP_WH_PIC_BLOGInfo_Output;

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian