Local Temporary Table in Oracle 10 (For the Scope of Stored Procedure)

Local Temporary table in Oracle 10 (for the scope of Stored Procedure)

You say you are new to Oracle. I'm guessing you are used to SQL Server, where it is quite common to use temporary tables. Oracle works differently so it is less common, because it is less necessary.

Bear in mind that using a temporary table imposes the following overheads:

1. read data to populate temporary table
2. write temporary table data to file
3. read data from temporary table as your process starts

Most of that activity is useless in terms of helping you get stuff done. A better idea is to see if you can do everything in a single action, preferably pure SQL.

---

Incidentally, your mention of connection pooling raises another issue. A process munging large amounts of data is not a good candidate for running in an OLTP mode. You really should consider initiating a background (i.e. asysnchronous) process, probably a database job, to run your stored procedure. This is especially true if you want to run this job on a regular basis, because we can use DBMS_SCHEDULER to automate the management of such things.

How to create and use temporary table in oracle stored procedure?

Just create it first (once, outside of your procedure), and then use it in your procedure. You don't want to (try to) create it on every call of the procedure.

create global temporary table tmp(x clob)
on commit delete rows;

create or replace procedure...
-- use tmp here
end;

Why its not necesary to check if a #temp table exist inside a store procedure in Sybase

Assuming Sybase ASE ... you can have only one #temp table (of a given name) at a given nesting/scope level.

At the connection level (eg, isql command prompt) you're at nesting/scope level 0. You can create a single instance of your #temp table here.

When you call a stored proc (or fire a trigger), you step down into a new nesting/scope level. You can create another instance of your #temp table at each new nesting/scope level.

NOTE: Keep in mind that when you exit the stored proc two key operations are performed ... 1) any #temp tables created by the proc are automatically dropped and 2) you exit the proc's nesting/scope level, ie, the nesting/scope level is decremented by one.

Consider the following example:

create proc child_proc
as
create table #t1(a int, b int)
select 'child_proc',name from tempdb..sysobjects where name like '#t1%' order by name
go

create proc parent_proc
as
create table #t1(a int, b int)
select 'parent_proc',name from tempdb..sysobjects where name like '#t1%' order by name
exec child_proc
go

create table #t1(a int, b int)
select 'top_level',name from tempdb..sysobjects where name like '#t1%' order by name
exec parent_proc
go

             name                 
 ----------  --------------------
 top_level   #t100000140011582650  -- command prompt

             name                 
 ----------  --------------------
 parent_proc #t100000140011582650  -- command prompt
 parent_proc #t101000140011582650  -- parent_proc

            name                 
 ---------- --------------------
 child_proc #t100000140011582650   -- command prompt
 child_proc #t101000140011582650   -- parent_proc
 child_proc #t102000140011582650   -- child_proc

A temp table name's format looks something like:

table-name + 17-byte suffix consisting of:    

     2-digit (0-padded) nesting/scope level
     5-digit (0-padded) spid
    10-digit (0-padded) number

For the above example my spid=14; the only difference in actual #temp table names (as seen by the system) is the nesting/scope level, which can be seen in the 4th/5th positions of the names:

#t1 00 00014 0011582650  -- command prompt; nesting/scope level 0
#t1 01 00014 0011582650  -- parent_proc;    nesting/scope level 1
#t1 02 00014 0011582650  -- child_proc;     nesting/scope level 2

How temporary tables behave in Stored Procedure of sybase

1: two executions of the same stored proc are completely independent (there may be some commonalities in terms of the query plan, but that does not affect the results)

2: see 1. temp tables are specific to the stored proc invocation and the user's session; temp tables are dropped automatically at the end of the proc (if you didn't drop them already).

3: there cannot be a locking/blocking issues on the temp tables themselves. But there can of course always locking/blocking issues on other tables being queried (for example, to populate the temp tables). Nothing special here.

oracle sql stored procedure that creates temporary local cache of number(10)

A global temporary table (GTT) is a perfectly fine way to do it if you do not know how many keys you might have because a GTT will spool to temporary storage rather than blow up your session memory.

But if you are absolutely sure that the size is small, then a nested table can do it, eg

SQL> set serverout on
SQL> declare
  2    emplist sys.odcinumberlist;
  3    l_count int;
  4  begin
  5    select empno
  6    bulk collect into emplist
  7    from scott.emp;
  8
  9    for i in 1 .. emplist.count
 10    loop
 11      dbms_output.put_line(emplist(i));
 12    end loop;
 13
 14    select count(*)
 15    into   l_count
 16    from   scott.emp
 17    where  sal > 2000
 18    and  empno in ( select column_value from table(emplist));
 19
 20    dbms_output.put_line('l_count='||l_count);
 21
 22  end;
 23  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
l_count=6

PL/SQL procedure successfully completed.

Local and global temporary tables in SQL Server

I find this explanation quite clear (it's pure copy from Technet):

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Global Temp Tables - SQL Server vs Oracle

Temporary tables in Oracle are permanent objects that hold temporary data that is session local. Temporary tables in SQL Server are temporary objects.

1. In SQL Server, a global temp table holds data that is visible to all sessions. "Global temporary tables are visible to any user and any connection after they are created." http://msdn.microsoft.com/en-us/library/ms186986.aspx
2. Global temp tables are still temporary objects that do not persist indefinitely, and may need to be created before use. "Global temporary tables are ... are deleted when all users that are referencing the table disconnect from the instance of SQL Server." http://msdn.microsoft.com/en-us/library/ms186986.aspx

I find that a local temporary table, or table variable, is the closest to being the same to Oracle's global temp table, the big difference is you have to create it every time.

Usually, in a case like yours, step 3, add rows to temp table, would be done by doing a select ... into #temp_table_name .... (equivalent to Oracle create table ... as select ...) http://msdn.microsoft.com/en-us/library/ms188029.aspx

Also, you can't do the following in a stored proc: (pseudo code.)

begin proc
   call another proc to create local temp table.
   use temp table
end proc

Local temp tables are destroyed when returning from the stored procedure that created them.

Update 2014-10-14: The behavior of local temp tables is different in the Parallel Data Warehousev version of SQL Server. Temporary tables are not dropped on exit from the stored procedure that created them, and instead continue existing for the rest of the session. This behavior observed on:

select @@version
Microsoft SQL Server 2012 - 10.0.5108.1 (X64) Jun 24 2014 20:17:02 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

---

Related Topics