Re: Global (and local) Temporary Tables & PL/SQL
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: andert@xxxxxxxxx
- Date: Tue, 3 Apr 2007 14:45:13 -0700
On 4/3/07, Stephen Andert <andert@xxxxxxxxx> 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
Other related posts:
