Re: temporary tables

Perhaps the best thing to do is ask yourself why you are even considering using a temp table in Oracle. If it is because that is what you did in SQL Server or Sybase: Stop!

In Oracle there is almost no need for them. And in the last ten years I have only had two occasions to use them at all. If the above is the reason ... don't use them. Use an in-line view instead.

Daniel Morgan

Richard Nield wrote:

> Ooops sorry about the blank reply above.
>
> Daniel....thanks for the reply. Just had a look through some of the
> question asked by others to support, and it would appear you are correct!
>
> Would seem my best bet is to use TYPE temp_tab IS TABLE OF UINS
>
> thanks......
>
> Richard
>
> "Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3C4A92E9.2E640F04_at_exesolutions.com...
> > Don't be so quick to thank him. Oracle does not build temporary table as
> you
> > requested. That is SQL Server.
> >
> > Oracle's global temporary tables are a permanent part of the Oracle data
> > dictionary. It is the rows in them that can either disappear after a
> commit or
> > disappear after a session: Something you determine when you define the
> table.
> > For example, the statement below, ON COMMIT PRESERVE ROWS deletes the rows
> when
> > the transaction ends.
> >
> > Read the manual or learn about how temp tables work in Oracle at
> > http://technet.oracle.com.
> >
> > Daniel Morgan
> >
> >
> >
> >
> > Richard Nield wrote:
> >
> > > Thanks...I'll go look.
> > >
> > > "pokalulu" <pokalulu_at_mindspring.com> wrote in message
> > > news:a2cuq9$9aa$1_at_nntp9.atl.mindspring.net...
> > > > Oracle can create temporary tables to hold
> > > > session-private data that exists for the duration
> > > > of a transaction or session.
> > > >
> > > > Example:
> > > >
> > > > CREATE GLOBAL TEMPORARY TABLE today_sales
> > > > ON COMMIT PRESERVE ROWS
> > > > AS SELECT * FROM orders WHERE order_date = SYSDATE;
> > > >
> > > > For more information:
> > > >
> > > > http://www.dbasupport.com/oracle/ora8/page2.shtml
> > > >
> > > >
> > >
> http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st
> > > > atements_73.htm#SQLRF01402
> > > >
> > > > Pokalulu-
> > > >
> > > >
> > > >
> > > > "Richard Nield" <richard.nield_at_ntlworld.com> wrote in message
> > > > news:c1m28.85661$_x4.8486883_at_news2-win.server.ntlworld.com...
> > > > >
> > > > > Hi
> > > > >
> > > > > I seem to remember there is a way you can create a temporary table
> > > that
> > > > > disappears when the session ends. Is my memory right, and if can
> > > someone
> > > > > give me a hint on how to do this?
> > > > >
> > > > > many thanks!
> > > > >
> > > > > .
> > > > >
> > > > >
> > > >
> > > >
> >
Received on Sun Jan 20 2002 - 15:17:07 CST