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?

Hi.
What we do, when we need a temporary table is, that we have a "permanent" table, but we never commit any insert, update or delete. In this way every change to the table is temporary. You can have several clients to use the table simultanously and everyones changes are only reflected to his own session. The procedure rolls back the changes to the table before it ends.

BUT!!!
Very often people are using temporary tables in PL/SQL, because they do not know a better way to make more complicated things in SQL than standard functions. If you want, let me analyze your actual problem. There might be an alternativ to writing a temporary table, that would be MUCHO, MUCHO, MUCHO faster.

Regards Tony Received on Sun Dec 01 1996 - 00:00:00 CET