Re: Assistance needed with creating tables in oracle.

On Sat, 11 May 2002, kpkeller_at_alltel.net wrote:

> In our application, we make use of temporary tables quite a bit.
> Consider the following example:
>
> Set SQLCA.AutoCommit = TRUE
>
> Create Table #TempVols(ObjID int, StartOil float, EndOil float)
>
> Insert Into #TempVols(ObjID, StartOil, EndOil)

I thought you could just run the second statement? I think there is a way to get a #tmp where you don't have to issue a create table statement and I thought it was the second statement alone. (Been awhile since I worked with #Tmps though)

> Select Distinct CompletionDaily.ObjectID, Sum(StartOilVol),
> Sum(EndingOilVol) From CompletionDaily, TankDaily Where
> CompletionDaily.ObjectID = TankDaily.ObjectID and RecordDate =
> '02-02-2002' and UserID = 400
>
> Update TankCompletionDaily Set BeginningOil = StartOil, EndingOil =
> EndOil From #TempVols Where ObjID = TankCompID and RecordDate =
> '02-02-2002' //or whatever date we want
>
> Just ignore the "set sqlca.autocommit = true" line because that is
> unique to Powerbuilder.
>
> I don't want to use a nested SELECT for each column being updated
> because it's too slow. The #Temp Table method is much faster as I
> have already tested it.

If you need the temp table (or if you would rather not change your logic per vendor) investigate global temporary tables. You don't create them on the fly. Oracle maintains the data per session, equivalent to the unique id for the #tmp table.

[...]

> Well first, when the temp table is created, both databases generate a
> unique value and append it to the temp table so that if any other
> users run this code simultaneously, they too will have their own temp
> table with different values assigned to their current
> transaction/connection session therefore eliminating the possibility
> of table name conflicts. Once the code above is complete, both
> databases (after the commit) will delete the temp table. Another
> reason for using a temp table is that we have some stored procedures
> that are quite long. We create the temp table at the beginning of the
> stored procedure and populate it with ONLY those records that meet our
> selection criteria and the rest of the stored procedure's queries and
> sql transactions can make use and even join against that temp table.

The global temp table would satisfy your criteria, and probably a bit faster than the #tmp, cause there is no ddl involved. Create the table with all the other tables real tables.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.