Re: Sybase vs Oracle: Temp Tables

ORACLE supports sub-queries to be used as tables. So you can put the select statement you want created as temp table in the sub-query. ORACLE will use the temporary tablespace to hold the results of the sub-query and drop it at the end of the query.
for e.g.

select dept.deptno, dept.dname, sq.dept_salary from
dept,
(select deptno, sum(sal) dept_salary from emp group by deptno) sq where dept.deptno = sq.deptno(+);

raghuvir

Andrew D. Arenson <arenson_at_swan.imgen.bcm.tmc.edu> wrote in article <wqg1f2rwb4.fsf_at_swan.imgen.bcm.tmc.edu>...
>
> My first question about differences between Sybase and Oracle
> in terms of bcp vs SQL*Loader got such a great response, that I'm going
> to try with a second question.
>
> In Sybase, I'm used to creating temporary tables on the fly
> without having to run the 'create table' command, by using the
> following syntax:
>
> select ...
> into #TMP_TABLE
> from ...
> where ...
>
> This doesn't work in Oracle (as far as I know). In fact, the
> 'SELECT ... INTO' syntax appears to be used for some sort of single
> row loading of data into variables that I haven't fully explored yet.
>
> So, I'm left with a two-part question:
>
> 1) Does Oracle allow one to create a table as the result
> of a select statement without having used the 'create table'
> command?
>
> 2) If so, does Oracle automatically clean up this dynamically
> created table at the end of the session, like Sybase does?
>
>
> Thank you in advance for any responses!
>
> Andy
>
> --
> Andrew D. Arenson |

http://gc.bcm.tmc.edu:8088/cgi-bin/andy/andy
> Baylor College of Medicine | arenson_at_bcm.tmc.edu (713) H
520-7392
> Genome Sequencing Center, Molecular & Human Genetics Dept. | W
798-4689
> One Baylor Plaza, Room S903, Houston, TX 77030 | F
798-5386
>
Received on Fri Aug 14 1998 - 21:40:11 CDT