We still use the temp tables and thanks for you help. I do understand that
sybase, sql server, and oracle are three separate products that function
differently. We are slowly beginning to fully understand the major and
minor differences between the three.
"Daniel A. Morgan" <damorgan_at_exxesolutions.com> wrote in message
news:3CDD1DCA.8AEF664E_at_exxesolutions.com...
> 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 - 22:48:28 CDT
