Hi

I want to create a Table dynamically inside a stored procedure and use the values in the table for many other SQLs inside the stored procedure. I was able to create the table using execute immediate but that table cannot be referenced in the SP. I am getting the following error.

Can any one suggest how to go about doing it in Oracle.

Regards

A table made using dynamic SQL can only be utilized via dynamic SQL.

You are most probably wrong using that approach; in Oracle, tables are created once (from SQL*Plus), and used many times (when necessary, including your PL/SQL script). Just in case, if you didn't hear about that feature, you might even use a global temporary table (maybe it'll be interesting for you):

yes, I had thought about Global temp tables also, But that needs to be created outside the Stored Procedure for using it in the SQL inside the SP. It would be nice to have CTE sort of functionality inside SP which can be used multiple times.

That's the point.

Why do you insist on creating tables in stored procedures? Oracle isn't designed to work like that, it is not MS SQL Server (which, as far as I read / heard) works that way.

As long as i am able to store result set of a query and use it in my SQLs inside the SP instead of running the same query multiple times.

Any how, I guess we just have to go with Global Temp Tables in Oracle. Just wanted keep the code as common as possible between databases.

Thanks for your valuable comments.

I'm not sure I understood what you said.

Presume that you create that table only once (regardless it is a global temporary table or not; maybe you don't need it at all, I just told you that it exists).

Yes, you can store result set of your query into a table that was pre-created. You can use it in your SQLs in your stored procedures any time. Kind of a "regular" process in a stored procedure would be:

• you DO NOT create a table in a stored procedure
• insert data into a pre-created table
• do whatever you want to do with it
• if you don't need it any more, delete data from a table (or even truncate the table)
• you DO NOT drop a table

Next time the procedure is executed, it re-uses the same table again.

What query is it that you don't want to run "multiple times"?

Please show us the full code. How should we know what you are doing in the SP without seeing the code?

It is most certainly possible to create a table in a stored procedure and use it.

But - just as the others have pointed out - it's definitely not a good idea.

Why don't you tell us the reall problem? Maybe there is a better solution to the problem.

Yes I Agree, In Oracle it is not possible / not good idea. My Point is, Why would any one want to create a table to store temporary result set who's life cycle starts and ends with in the Stored Procedure.

Any how as i have mentioned, We are now using GTT.

Well that's exactly what GTTs are for.
The fact that the table definition is always available does not do any harm, nor does it have any negative impact.