How to Create a Temporary Table in an Oracle Database

How do you create a temporary table in an Oracle database?

Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation.

However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle.

CREATE GLOBAL TEMPORARY TABLE today_sales(order_id NUMBER)
ON COMMIT PRESERVE ROWS;

---

Oracle 18c added private temporary tables, which are single-session in-memory objects. See the documentation for more details. Private temporary tables can be dynamically created and dropped.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_today_sales AS
SELECT * FROM orders WHERE order_date = SYSDATE;

---

Temporary tables can be useful but they are commonly abused in Oracle. They can often be avoided by combining multiple steps into a single SQL statement using inline views.

Creation of Oracle temporary table with same table structure to that of a existing table

Global temporary tables in Oracle are very different from temporary tables in SQL Server. They are permanent data structures, it is merely the data in them which is temporary (limited to the session or transaction, depending on how a table is defined).

Therefore, the correct way to use global temporary tables is very different to how we use temporary tables in SQL Server. The CREATE GLOBAL TEMPORARY TABLE statement is a one-off exercise (like any other table). Dropping and recreating tables on the fly is bad practice in Oracle, which doesn't stop people wanting to do it.

Given the creation of a global temporary table should a one-off exercise, there is no real benefit to using the CREATE TABLE ... AS SELECT syntax. The statement should be explicitly defined and the script stored in source control like any other DDL.

---

You have tagged your question [oracle18c]. If you are really using Oracle 18c you have a new feature open to you, private temporary tables, which are closer to SQL Server temporary tables. These are tables which are genuinely in-memory and are dropped automatically at the end of the transaction or session (again according to definition). These are covered in the Oracle documentation but here are the headlines.

Creating a private temporary table data with a subset of data from permanent table T23:

create table t23  (
    id number primary key
    , txt varchar2(24)
    );

insert into t23 
select 10, 'BLAH' from dual union all 
select 20, 'MEH' from dual union all 
select 140, 'HO HUM' from dual
/


create private temporary table ORA$PTT_t23 
on commit preserve definition
as 
select * from t23
where id > 100;

The ORA$PTT prefix is mandatory (although it can be changed by setting the init.ora parameter PRIVATE_TEMP_TABLE_PREFIX, but why bother?

There after we can execute any regular DML on the table:

select * from ORA$PTT_t23;

The big limitation is that we cannot use the table in static PL/SQL. The table doesn't exist in the data dictionary as such, and so the PL/SQL compiler hurls - even for anonymous blocks:

declare 
    rec t23%rowtype;
begin
    select * 
    into rec
    from ORA$PTT_t23';
    dbms_output.put_line('id = ' || rec.id);
end;
/

ORA-06550: line 6, column 10: PL/SQL: ORA-00942: table or view does not exist

Any reference to a private temporary table in PL/SQL must be done with dynamic SQL:

declare 
    n pls_integer;
begin
    execute immediate 'select id from ORA$PTT_t23' into n;
    dbms_output.put_line('id = ' || n);
end;
/

Basically this restricts their usage to SQL*Plus (or sqlcl scripts which run a series of pure SQL statements. So, if you have a use case which fits that, then you should check out private temporary tables. However, please consider that Oracle is different from SQL Server in many aspects, not least its multi-version consistency model: readers do not block writers. Consequently, there is much less need for temporary tables in Oracle.

How to create a temporary table inside Stored Procedure before opening a cursor?

Your procedure won't compile because you have dependencies on TEMP_TABLE which does not exist. Hence the ora-00942. Of course, if it does exist then your procedure will fail at runtime: the create global temporary table call will fail, because the table already exists.

Basically you have misunderstood the purpose of global temporary tables in Oracle. They are permanent structures, it is just the data they hold which is transient. This is a common issue, especially for people who are familiar with SQL Server and are trying to convert T-SQL into Oracle. Temporary tables in MSSQL are more like PL/SQL collections.

Obviously your posted code is a toy so it's not clear why you think you need a temporary table. It's quite likely that you don't, as Oracle SQL is pretty powerful. Chances are you can just open a ref cursor for a complex SELECT statement.

But it you do happen to have some real need this is how to do it:

First, as a one-off exercise:

create global temporary table my_temp_table
   (column1 number) on commit delete rows   
    tablespace temporary_ts;

Note the tablespace clause: GTTs write to disk not memory, which means they are slow to populate and slow to read. If you're going to use GTTs it's a good idea to have a dedicated temporary tablespace just for them, because they have a different usage profile compare to other temporary processes such as sorts.

Anyway, your procedure becomes

PROCEDURE DELETME (
          O_CURSOR         OUT tCursor,
          COD_ERROR        OUT NUMBER,
          MSM_ERROR        OUT VARCHAR2
          )
     AS
BEGIN
    insert into my_temp_table(column1) values (1);
    COD_ERROR := 1;
    OPEN o_cursor FOR
           select * from my_temp_table;
EXCEPTION 
    WHEN OTHERS THEN
              COD_ERROR:=0;
              MSM_ERROR:=dbms_utility.format_error_backtrace ||' '||SQLERRM;
END DELETME;

Remember you need to issue a commit (or rollback) to clear your table; if you don't housekeep it that may create problems when the same session re-uses it.

Alternatively, use a collection instead. Collections are much faster, because they are memory structures. Although the memory comes from session allocations so this is not the best solution if the total number of rows is too large.

Something like this. Again, as a one-off exercise:

create or replace object num_nt as table of number;

Then your procedure becomes:

PROCEDURE DELETME (
          O_CURSOR         OUT tCursor,
          COD_ERROR        OUT NUMBER,
          MSM_ERROR        OUT VARCHAR2
          )
     AS
    local_nt num_nt;
BEGIN
    select 1 
    bulk collect into local_nt
    from dual;
    COD_ERROR := 1;
    OPEN o_cursor FOR
           select * from table(local_nt);
EXCEPTION 
    WHEN OTHERS THEN
              COD_ERROR:=0;
              MSM_ERROR:=dbms_utility.format_error_backtrace ||' '||SQLERRM;
END DELETME;

---

There is a third "solution" which is to use dynamic SQL for all the calls in the procedure. This is a really bad approach (even apart from misunderstanding the use of global temporary tables). Dynamic code is flakier than regular code, and should only be used when really necessary. Executing DDL is expensive and not something to be done as part of standard business processing; also it complicates transactions.

Can we create a temporary table, getting a result in oracle through R and delete table afterwards?

I don't know R.

However, consider moving code you have into a stored procedure. You'd then - in a single line (hopefully) - call that procedure from R. It would do its job (populate the table) and you'd just use its contents in R.

---

Related Topics