Need some assistance creating a table in an 8i database

Hello all:

We are building support into our Powerbuilder 7.0 application to allow our customers to utilize their Oracle databases (we are testing against 8i).

Right now our application has support for Microsoft SQL Server 7.0 and 2000 as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7. Oracle (later DB/2 as well) support is being added now.

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) 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.

What's nice about this, is that I can create it in a stored procedure or within Powerbuilder using Dynamic SQL and Powerbuilder's Transaction Object. The stored procedure is a bit faster than doing it in PB, however.

How can I accomplish this same task within 8i using a temp table? The reason for the temp table is that both Sybase and SQL Server handle the temp table a bit differently from a normal table.

In what ways you ask?

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.

Any suggestions? Received on Sat May 11 2002 - 11:30:40 CDT