temporary tables

temporary tables

Post by trumpet.. » Sun, 31 Dec 1899 09:00:00



How do you create a temporary table within a stored procedure?  I
remember being able to do this in SQL Server but I cannot find a way
to do it in Oracle v 7.3.4.

Can someone post an example ?

Thanks.

Mike

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

temporary tables

Post by Christopher M. Da » Sun, 31 Dec 1899 09:00:00


Mike,

Temporary tables managed by the database are introduced in Oracle8i.

So in 7.3.4 we have to manage our own using CREATE TABLE xx AS .. where
xx is our unique generated table name. Remember to use the storage
clause...

Chris.

I remember being able to do this in Informix 2.1 10 years ago ....


> How do you create a temporary table within a stored procedure?  I
> remember being able to do this in SQL Server but I cannot find a way
> to do it in Oracle v 7.3.4.

> Can someone post an example ?

> Thanks.

> Mike

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

temporary tables

Post by Ed Procha » Sun, 31 Dec 1899 09:00:00


But using CREATE/DROP of temporary tables is overkill in many
situations.

Does Mike really need a table that can be queried? Or do you just need
temporary storage, like an array. Look into PL/SQL arrays. I cannot give
an example today, but check you PL/SQL references. The arrays work just
like a sparse array, can hold records from queries. Only thing you
cannot do is put it in an SQL query (cannot SELECT FROM it.) No need to
worry about naming conflicts either!

So rather than forcing SQL Server or Informix methods into your Oracle
procedures, why not describe the real problem you are seeking to solve?
then we can suggest how best to solve it in Oracle.



> Mike,

> Temporary tables managed by the database are introduced in Oracle8i.

> So in 7.3.4 we have to manage our own using CREATE TABLE xx AS ..
where
> xx is our unique generated table name. Remember to use the storage
> clause...

> Chris.

> I remember being able to do this in Informix 2.1 10 years ago ....


> > How do you create a temporary table within a stored procedure?  I
> > remember being able to do this in SQL Server but I cannot find a way
> > to do it in Oracle v 7.3.4.

> > Can someone post an example ?

> > Thanks.

> > Mike

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

temporary tables

Post by Emmanue » Sun, 31 Dec 1899 09:00:00


I agree completely with this. Creating and dropping database objects
creates a lot of system overhead !

Why not use views or dynamic views (select .... from (select ... from
..)).



> But using CREATE/DROP of temporary tables is overkill in many
> situations.

> Does Mike really need a table that can be queried? Or do you just need
> temporary storage, like an array. Look into PL/SQL arrays. I cannot give
> an example today, but check you PL/SQL references. The arrays work just
> like a sparse array, can hold records from queries. Only thing you
> cannot do is put it in an SQL query (cannot SELECT FROM it.) No need to
> worry about naming conflicts either!

> So rather than forcing SQL Server or Informix methods into your Oracle
> procedures, why not describe the real problem you are seeking to solve?
> then we can suggest how best to solve it in Oracle.



> > Mike,

> > Temporary tables managed by the database are introduced in Oracle8i.

> > So in 7.3.4 we have to manage our own using CREATE TABLE xx AS ..
> where
> > xx is our unique generated table name. Remember to use the storage
> > clause...

> > Chris.

> > I remember being able to do this in Informix 2.1 10 years ago ....


> > > How do you create a temporary table within a stored procedure?  I
> > > remember being able to do this in SQL Server but I cannot find a way
> > > to do it in Oracle v 7.3.4.

> > > Can someone post an example ?

> > > Thanks.

> > > Mike

> --
> Ed Prochak
> Magic Interface, Ltd.
> ORACLE Development, conversions, training and support

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

temporary tables

Post by Steve Urba » Sun, 31 Dec 1899 09:00:00


I was wondering about this also (been Reading "Oracle8: The Complete
Reference" and "Oracle8: Beginners Guide" and had not run across it
yet. I am brand new to Oracle (that is why I have been Lurking here
<g>).

I have been using Paradox DOS since V1.1 days, We always had "Private
tables available (both the standard system ones like Answer, List...
and those the programmer Declared).
Using these allowed me to acomplish many things: Reduce Network
Traffic, Create a initial SubSet of the Data that would have MANY
further operations performed.

As best I can tell , Using Private Tables or Using More Complex
Queries could be a trade-off on system hits.  Since I don't have 8i,
it is probably a moot point
I would still like to hear more so that I can learn a new "stye' of DB
use.

Steve U


>I agree completely with this. Creating and dropping database objects
>creates a lot of system overhead !

>Why not use views or dynamic views (select .... from (select ... from
>..)).



>> But using CREATE/DROP of temporary tables is overkill in many
>> situations.

>> Does Mike really need a table that can be queried? Or do you just need
>> temporary storage, like an array. Look into PL/SQL arrays. I cannot give
>> an example today, but check you PL/SQL references. The arrays work just
>> like a sparse array, can hold records from queries. Only thing you
>> cannot do is put it in an SQL query (cannot SELECT FROM it.) No need to
>> worry about naming conflicts either!

>> So rather than forcing SQL Server or Informix methods into your Oracle
>> procedures, why not describe the real problem you are seeking to solve?
>> then we can suggest how best to solve it in Oracle.

,        _
,       | \                           Steve Urbach
,       |  )erek

,  / / /                              http://www.greenduck.com
 
 
 

1. How temporary are temporary tables?

Hi There,

We are running IngresII 2.0/0001 Patch 7336 on Dec Alphas with DUNIX 4.0G.

We are experiencing some inconsistent results with session temporary tables.
Specifically we have 'lots' of sql code which does something like:
    declare global temporary table session.temp_sub_dev_off as
        select varchar(subcode) + '|' + subcode_desc
            from s_subcode_table
            where valid_for_cur_year = 'Y'
            on commit preserve rows
            with norecovery;
        \p\g
        copy session.temp_sub_dev_off(col1= char(0)nl)
            into '/home/usr/asis/sofi/transfer/data/advance_sc_so';
        \p\g

However, when executed we occasionaly receive errors:
E_US0845 Table 'temp_sub_dev_off' does not exist or is not owned by you.
    (Wed Dec 19 03:00:43 2001)

These errors are in the sql session and not in the error log.
The errors are not generated consistently, we can execute the same code at other
times and things succeed. We can immediatly follow the code that fails with an
identically structured query (ie. create temp table;copy temp table) and this
works.

The tables are small, often only a few hundred rows. The Cache has 16000 single
pages and 250 x 160page group buffers. The write behind water marks are set
very low, so I cant see this being a cache issue.

Being SQL code we would run with a startsql file:
set lockmode session where readlock = nolock;
set maxio 135000;
set maxrow 3000000;
set session with on_error = rollback transaction;

ING_SYSTEM_SET includes a file that specifies:
set lockmode session where maxlocks = 950;
set result_structure heap;

Furthermore we have the environment variables:
II_TM_ON_ERROR=nocontinue
II_TM_SWITCH=true

Any ideas?

Martin Bowes
--
Random Earthworm Jim Quote #10:
Jim - What sort of Elves are you anyway?
Elf - Actually we just call ourselves Elves to attract the chicks.
Jim - Does it work?
Elf - Not even a little

2. sybase erorlog

3. Copying the structure of a table into a temporary table without constraint and identities

4. identity columns

5. Temporary tables VS real tables

6. FK Constraint Flag Automagically Appears

7. Temporary tables VS 'temp-tables'

8. Free database course is running, join now!

9. instead of delete trigger delete data from table using execute and temporary table for deleted

10. check existance of a temporary table before drop this table

11. Pass table variable or temporary table as parameter?

12. Creation of temporary tables in SP, accessing the temp table in ASP

13. Derived table versus temporary table performance issue