Re: Compatible parameter

DenHang wrote:

> I'm writing a rather complex query that I've partitionated in several steps.
> After the first part, I have an intermediate resultset that I need to query
> in the following step. For several reasons I want(ed) to use temp tables:
>
> - Habit (temp tables are indeed frequently used in Ms SQL server)
> - I try only to use standard SQL (not a must!)
> - The need to perform a select statement on the intermediate resultset.
>
> Since I hadn't too much time lately, I haven't (yet) been looking for
> alternatives (eg. PL/SQL, cursors...). If someone could give me a hint of
> better ways to do this, I would be very gratefull.
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D35A11A.CA9894A6_at_exesolutions.com...
> > DenHang wrote:
> >
> > > When I try to create a temporary table (using CREATE GLOBAL TEMPORARY
> > > TABLE...) I get the following error:
> > > ORA-00406: COMPATIBLE parameter needs to be 8.1.0.0.0 or greater
> > >
> > > I'm getting this error using SQL plus and Pl Sql developer and I
> installed
> > > Oracle 8i release 3.
> > >
> > > Is it wise to change that parameter and how can I change it?
> > > Is there may-be another way to create temporary tables (eg. like in Ms
> Sql
> > > where you can create temp tables local to a session using # in front of
> the
> > > tablename)?
> >
> > Sybrand is correct about the init parameter. Just keep a backup copy of
> your
> > initSID.ora file so that you can easily undo any changes if you make a
> mistake.
> >
> > But in Oracle we rarely, if ever, use temporary tables for anything:
> Global
> > temporary or otherwise. They are just unnecessary. And that is what
> Sybrand was
> > refering to when he suggested you stop trying to write TSQL in Oracle.
> >
> > I can't think of more than two or three times in the last decade I've
> actually
> > found a need to use a temporary table.
> >
> > If you think you have a reason why you need one then post it here and let
> us
> > help you break that bad habit. Remember Oracle does not have SQL Server's
> > weaknesses in locking, blocking, and transaction size.
> >
> > Daniel Morgan
> >

This is not a reason for a temorary table in Oracle.

In Oracle this is easily accomplished by what are called IN-LINE VIEWS (in TSQL derived tables). The form very simly is as follows:

SELECT field, field, field
FROM (
   SELECT field, field, field
   FROM
   WHERE
   AND ... ) alias,
  another_in-line_view alias,
  a_table,
  another_table
WHERE
GROUP BY
HAVING
ORDER BY We don't have limited locks or a need to keep transactions small. Use temporary tables on the above and you kill scalability and performance.

Daniel Morgan Received on Wed Jul 17 2002 - 17:42:16 CDT