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

From purely coding point of view, there is a bit dangerous, but quick way of fixing the problem.  

If it is only a couple of places where you need to use that temporary table - just convert all calls to dynamic SQL!  

Nice thing about the solution - you can make the name of table dynamic (for example, include user id into it), so you will not have to worry how many users are trying to manipulate with the table at the same time.

Negative side, of course, is the overhead of the dynamic SQL (timing, lost dependencies etc).  

Stepping to the side... If it would not be couple hundred thousand rows - I would propose some kind of collection (either object collection or associative array - depending on what exactly is needed). But if the server has a lot of memory - why not? Personally, I prefer to use collections (as long as the number of records is reasonable) - but it is a trade-off between using the memory and using temporary tablespace.  

Regards,

Michael Rosenblum

From: oracle-l-bounce_at_freelists.org on behalf of Stephen Andert Sent: Tue 4/3/2007 4:29 PM
To: oracle-l_at_freelists.org
Subject: Global (and local) Temporary Tables & PL/SQL

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 03 2007 - 16:09:21 CDT