Temporary tables in PL/SQL, are those bad ?

I read this article in one of the Oracle's user forum...
How to write a code for using temporary tables in PL/Sql ???
One way to do this is to define a record in the package Specification, which contain the colums you want...and then built a table based on this record...
*******************************************
Create Package name_pkg AS
TYPE employee_rec IS RECORD
/* define colums directly or via colums in
a table, which means same dataformat */
(deptno dept_table.deptno%TYPE,
empno NUMBER, empname VARCHAR2(40));
TYPE employee_tab IS TABLE OF emplyoee_rec INDEX BY BINARY_INTEGER;
*********************************************
"There's an inherently poor issue about using DDL within PL/SQL at all, it limits concurrency, saps performance, and clobbers multi-user capability in the database. Generally speaking, when there are genuine occasions to use temporary tables (these are rare, but usually for performance or denormalization) it is best to actually use a PL/SQL table, rather than a temporary table. They're instantiated independently and don't suffer from most of the drawbacks of temporary tables. When you do this, it's best to use %TYPE to avoid latent dependencies"

Is this true ? Are temporary tables a bad choice ?
We use them at many places in our application when we want to copy tree or move the tree... to store intermediate records.
We do have reported timeout errors some times. Can these temporary tables be culprit ??

It is not clear if this article is talking about real temporary tables, that is GLOBAL TEMPORARY TABLES as they exist in 8i (if it is, I can't understand why it is talking about DDL, concurency and multi-user isues etc...). Global temporary tables are tables that are permanently created in the database, but the rows in it are visible only by the user who created them and are persistent only for the duration of a transaction or a session.

Both global temporary tables and PL/SQL tables have their benefits and drawbacks, but they generally can not be compared to eachother directly. They each can be a better or worse choice in a given situation (sometimes one or the other can simply can not be used, is not aplicable).

The main difference between the two is the fact that PL/SQL table is a simple array of values or records that can be only reached sequentialy by reading each row of an array or by knowing the binary_integer keys for those rows, while on temporary tables you can actualy perform queries and DMLs. Also, PL/SQL tables can generally be used only to store relativelly small amount of data, while the global temporary tables behave more or less like any other table.

Can you help me with the script to create a Global temporary table ? We have 8i now.

thanks a lot
sonali kelkar

An example from an SQL manual:

CREATE GLOBAL TEMPORARY TABLE flight_schedule (
startdate DATE,
enddate DATE,
cost NUMBER)
ON COMMIT PRESERVE ROWS;

So it is exactly the same as creating normal tables except for "GLOBAL TEMPORARY" and "ON COMMIT PRESERVE ROWS" parts.

"ON COMMIT PRESERVE ROWS" means the records you'll creat inthere will not disapear after you perform a commit, in other words, it will remain there while your session lasts if you don't delete them with a DELETE sql command.

The other option is "ON COMMIT DELETE ROWS" means the records will be wisible only during your transaction and will disapear after you perform a COMMIT.

In any case the records each session creates are not visible outside to other sessions.

After the table is created you can create indexes on it, grant it to other users, create synonyms for it etcetc, much more like an ordinary table.

Thanks a lot