Re: Problem with temporary tables

Bobby V. wrote:
>
> In another application with SQL Server as back end we were able to use
> temporary tables in stored procedures like:
>
> create table #temp (proj_no char(10), fees decimal(15,2));
> Q1: How to create any temporary table in STORED PROCEDURE in Oracle
>
> insert into #temp (proj_no, code, fees)
> select proj_no, sum(amount*multiplier) from project
> group by proj_no, ;
>
> Above statement would do a fast transfer and massage of data into
> temporary table (cursor specific,
> no logging etc.), and would be easily drop once stored procedure is
> finished.
>
> Could we do something like this on Oracle?

In Oracle you can create a table and fill it with data like this:

create table newtab as select proj_no, sum(amount*multiplier) from project group by proj_no, ;

This table is created according the projection of the select statement and filled with the data found in the select statement.

It is not a temporary table which is dropped after closing the session, so you have to care about dropping it.  

Peter R. Messerer        Tel.:   08102 / 748140
Ahornring 33             Fax :   08102 / 748139
85635 Siegertsbrunn      email:  Peter.Messerer_at_munich.netsurf.de
Germany	                         CompuServe: 100714,3206

http://homepages.munich.netsurf.de/Peter.Messerer/