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: rjamya <rjamya_at_gmail.com>
Date: Tue, 3 Apr 2007 23:39:44 -0400
Message-ID: <9177895d0704032039p462a80d6p2005bc79f0ba6d56@mail.gmail.com>


Not sure about the "wise" part, but answers inline ....

On 4/3/07, Stephen Andert <andert_at_gmail.com> wrote:
>
>
> 1. What factors should influence global or local temporary (or even
> "real" tables if they may be better)?

GTTs are ideal for you, for this purpose. As long as you don't over index them and avoid truncating them.

2. Is there a way to "force" PL/SQL to "trust me, the table will be
> there" to get the proc to compile.

AFAIK, NO, but you can work around this simply by performing all DML and SELECTs on those GTTs using dynamic sql. Nothing is cheap. The better solution is to have a fixed GTT name (with preserved rows on commit so data can live beyond transaction if needed).

3. What else should I be thinking about that I am not (since I just got
> blind-sided with this).

GTTs can be addictive, so moderation is highly recommended. Sometimes GTT may appear like a big hammer and everything else a small nail, do not fall for that illusion.

rjamya

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 03 2007 - 22:39:44 CDT

Original text of this message

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