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

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