Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Global (and local) Temporary Tables & PL/SQL

Re: Global (and local) Temporary Tables & PL/SQL

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 3 Apr 2007 22:35:53 +0100
Message-ID: <7765c8970704031435u3cb20b05vac6dde50f29b645c@mail.gmail.com>


normally one would create the table up front, just like a normal table, other RDBMS systems do use 'temporary tables' that are created and destroyed as part of the procedure, Oracle's temporary tables are designed to be permanent objects with temporary contents. So I'd say move the creation out of the procedure, but leave the population in it.

cheers

On 4/3/07, Stephen Andert <andert_at_gmail.com> wrote:
>
> Hello wise ones!
>
> I was just approached with a question/problem. A developer (not a
> DUHveloper, this guy is usually pretty good) wants to investigate
> using temporary tables for intermediate processing. He is trying to
> test this but when he modifies his PL/SQL to use the global temporary
> table, it won't compile as the table is created and populated in the
> same step and thus does not exist when trying to compile the
> procedure.
>
> The facts known at this time are:
>
> 1. This table may be somewhat large (several hundred thousand rows
> with several VARCHAR 255 fields) at first, but later will be much more
> reasonable.
>
> 2. Many users will be using this table at the same time with differing
> data and processing other data based on what they do with the data in
> these temp tables.
>
> The questions are:
>
> 1. What factors should influence global or local temporary (or even
> "real" tables if they may be better)?
>
> 2. Is there a way to "force" PL/SQL to "trust me, the table will be
> there" to get the proc to compile.
>
> 3. What else should I be thinking about that I am not (since I just
> got blind-sided with this).
>
> Thanks In Advance.
>
>
> --
> Stephen Andert
> http://andertfamily.net/racing_reports.aspx
>
> Any idiot can run.
> It takes a special kind of idiot to run a marathon.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 03 2007 - 16:35:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US