Stephen,The purpose of "global" temporary tables (GTTs) is to exist permanently, not to be created "on the fly", for just that very reason. Why do they feel they have to create these tables "on the fly"? Why can't they just create them once and leave them alone? Creating and dropping "on the fly" is just begging for other sets of problems (i.e. database thrashed by DDL, permissions granted inappropriately leading to human error, etc)...
Regardless, they'll have to move to dynamic SQL within the PL/SQL procedure in order to get around this chicken-and-egg problem, if they want to persist on this route.
Hope this helps! -Tim Quoting Stephen Andert <andert@xxxxxxxxx>:
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