Hi folks,

It seems to me that the original question has not been answered, and 5 years later I now have the same question :( Any help appreciated.

I have a PL/SQL proc running in 10g.
In this procedure I execute a sequence of queries within a series of For Loops. The reult is the correct answer but terrible performance, and so it's been suggested that I try using GTTs (which I've never used before).

Now while I have found many pages describing the syntax of creating GTTs, I can nevertheless find nothing that explains how I might use them in my situation. From within the PL/SQL proc I would like to create a GTT using the 'AS subquery' syntax, thus providing a GTT with simplified data for me to access later within the same proc and called subprocs.

So at the start of my PL/SQL proc I tried to create the desired GTT, but I get a compile error complaining that it didn't expect the 'CREATE' keyword at that point.

a) So as in the original post, why can't I create a GTT from within a PL/SQL proc?
b) Ales said it's bad practice to try this. Why? At the moment it doesn't even seem possible, nevermind bad practice.
c) If this is not possile, then I don't understand how I am supposed to use temp tables to assist my performance issue? They are supposed to be used to store intermediate data. Isn't that what I am trying to do?

Many thanks for your help
Assaf