Execute Immediate and DDL

Today I created a script which creates DDL statements on the fly for doing data conversion between Loyalty phase 1 and 2. I discovered that when doing:


begin
execute immediate('create table MYTABLE(......)');
insert into MYTABLE(select * from ORIGINAL_TABLE);
end;

The above code doesn't compile. I get ORA-00942: table or view does not exist.

However when I do this:

begin
execute immediate('create table MYTABLE(......)');
execute immediate('insert into MYTABLE(select * from ORIGINAL_TABLE)');
commit; -- don't forget the commit, execute immediate doesn't do this for you automatically
end;


Then it works. Which kind of makes sense because the compiler doesn't recognize that table until it gets half way through the script execution. So if you dynamically need to create a table and then insert into it, you have do dynamically insert it using execute immediate.