how to create and insert into a table in an anonymous block

Question

I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.

When the block compiles, it gives me an ORA-00942: table or view does not exist error.

Well, no, it doesn't exist, but it will be created before the insert occurs. If I create the table before compiling, it will work.

How can I handle this?

• Philᵀᴹ: Do the insert using execute immediate.

Answer 1

Generally, it doesn't make sense to create tables in anonymous blocks let alone to then insert data into those tables in the same anonymous block. Creating new objects at runtime is a bad idea (hopefully you're not trying to create a temporary table like you would with other databases).

If you really do need to create a new table at runtime, you can insert data in the same anonymous block only by using dynamic SQL for the INSERT operation as well. You would need to use dynamic SQL every time you referenced the newly created table in the same anonymous block. You could also create the table in one anonymous PL/SQL block and then insert the data in a second anonymous PL/SQL block. That would not require that the INSERT statement use dynamic SQL.

Since it sounds from the comments like you are trying to create a temporary table like you would in other databases, you probably want to take a look at this StackOverflow thread that discusses alternatives to creating temporary tables in Oracle.

• JHFB: Of course I am trying to create a temporary table like I would with other databases - I just moved from SQL Server to Oracle a month ago :) I think my original requirements for this block may spawn a new question...
• Philᵀᴹ: Be aware that temporary tables are rarely used in Oracle when compared with SQL Server. You may need to readdress your requirements/design
• Justin Cave: @JHFB - You may want to take a look at the thread I just added a link to before writing that new question.
• Colin 't Hart: Oracle also has temporary tables, but with different semantics. These are tables that exist permanently but the data is temporary, either "disappearing" at commit or when the session ends. The only time I've had reason to use these is as a temporary holding table for inserts which can potentially be from queries operating over a database link. By using the temporary tables I can maintain a real table as the source of my data without the hard coupling of a view over a database link -- which wouldn't work in my situation anyway because which database link to use is a parameter to my procedure.
• JHFB: @JustinCave - I think the link addresses the second question I just asked!