stored procedure and table creation

How do I code a stored procedure which needs to create tables in it (temporary tables required for processing).

If I code the create table within an 'execute immediate', the tables do not exist at compile time, and the procedure does not compile.

If I create the tables, compile the procedure, and then drop the tables so they can get recreated at runtime, the procedure is invalidated.

What is the right way to do this? Also, do I have to use execute immediate for all sql execution? Or can I do selects, inserts and updates directly without the 'execute immediate'?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 15 2000 - 00:00:00 CDT