RE: SELECT INTO new_table

Gina,

        Regrettably this is one place where Oracle has something to learn from their open source competition. In PostGreSql (Yeah, my favorite Open Source DB) you can issue a:

        create temporary table xxx as select <>

And the entire table disappears from existence when your session ends. In Oracle land you still have to create a persistent object, called a global temporary table. Better, but not quite Miller time.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Gints Plivna Sent: Monday, November 21, 2005 5:57 AM
To: dominik_at_ttsm.net
Cc: oracle-l_at_freelists.org
Subject: Re: SELECT INTO new_table

Most probably you'd like to revisit your programming approach and not to use the very same technique like in MSSQL and Transact-SQL. Yea, I'v seen technique to use many temporary tables in Transact-SQL procedures (and was horrified by it) but although it may be acceptable and advisable in MSSQL, it definitely isn't advisable in Oracle. One direct way to avoid it is to use global temporary tables, other ways probably aren't directly related, they might be task-specific ways.

Here is a link in asktom about this problem: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:66306 20415602

Gints Plivna

On 11/21/05, Dominik Smatana <dominik_at_ttsm.net> wrote:
> Hello,
> is there any way in Oracle to dynamically create a new temporary table
> direct from select?
>
> Like In Transact SQL :
> SELECT <something> INTO <new_table> FROM....
>
> Thanks.
> Dominik Smatana

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l