Re: Assistance needed with creating tables in oracle.

"KPK" <kpkeller_at_alltel.net> wrote in message news:abj9vb$lqd$1_at_iac5.navix.net...
> 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?
>
>
>

1 Try to learn Oracle, or better still try to learn SQL. Sucessful one on one ports from Sqlserver to Oracle don't exist, because Oracle is not sqlserver sold by a different vendor. If I have to investigate performance problems on a database which is used for an application which has been ported from Sqlserver to Oracle, that is ALWAYS the root cause: the developers involved simply didn't know anything about Oracle. Read the initial chapters of Tom Kyte's book and you will see this demonstrated into more detail

2 If you *really* think you can't do without temp tables (and the above example shows you resort to temp tables for simply *everything*) , check out the Oracle manuals on GLOBAL TEMP tables.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address