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

On 4/3/07, Stephen Andert <andert_at_gmail.com> wrote:
>
> 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.

Though it is not clear from the email, it would seem that the tables in question are created at runtime. Is that correct?

Otherwise the table(s) would just need to be created before the package is compiled.

We have a production app with a similar catch 22. The global temporary table is created by the procedure. I can't compile it unless I first create the table. The procedure can then drop and recreate the table at will.

An interesting side effect from this is that the procedure will go invalid if it is running and the session crashes or the database goes down after the 'drop table' code. When the database is back up, the table is gone, and you may have to ponder for a few minutes what is going on when you try to recompile. The table is missing, the DDL to create it is in the procedure. Oh.

If it is not feasible to precreate the table(s) due to the table names not being known in advance, then you my want to use dynamic SQL when working with the global temporary tables.

> 1. What factors should influence global or local temporary (or even
> "real" tables if they may be better)?

No redo is generated directly, which will aid in response time and eliminate unnecessary redo ( make sure the data is expendable). Undo is however generated, and redo for the undo is also generated. 1 out of 3 ain't bad.

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

I don't think so. Maybe someone else knows of something, but I don't of a way to force Oracle to parse code with non-existant components.

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

How to get someone else to do this for you. :)

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l