Re: Need some assistance creating a table in an 8i database

KPK wrote:

> 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?

In your application you DID make use of temporary tables. Did being past tense.

You can certainly do it in Oracle using native dynamic SQL. For example:

x := 'CREATE TABLE x (myfield varchar2(20))'; EXECUTE IMMEDIATE x;

But doing so while a necessity in SQL Server is a great way to create a mess in Oracle. In Oracle transactions should be as large as is logically valid. No temp tables. No incremental commits. Etc. The entire model is different. So if you just port your existing code and architecture to Oracle you will make an unscalable poorly performing mess that may well corrupt data.

I have been involved in quite a few SQL Server to Oracle migration projects and inevitably they start out with people thinking, somehow, that they are the same product from two different vendors. Nothing could be further from the truth. Eventually, usually after stubbing their toes, all either decide to treat Oracle as what it is or they fail miserably.

If you insist on using temporary tables in Oracle use global temporary tables. Which are not tables that you create on-the-fly but rather permanent tables that provide the same functionality without the performance hit.

Daniel Morgan Received on Sat May 11 2002 - 08:34:02 CDT