Dynamic SQL not working as expected

Question

create or replace procedure createtables
  Authid current_user as
begin
  execute immediate 'create table newcustomer as select * from customer';
end;

create or replace procedure e
is
begin
 createtables;
 select * from newcustomer; 
end;

I got two procedures above. first one will create a new tables called newcustomer, second procedure will call the first procedure and query to the newcustomer table. when I try to compile this code, it says the table is not yet created, I don't really get it as I have called createtables procedure so I assume I have created the table.

Any help will be appreciated. Thanks

Answer 1

Compiling the second procedure without executing the first procedure first will fail, since the table has not been created.

You cannot compile a procedure that relies on objects that do not exist.

Use EXEC createtables before creating procedure e, and do not call createtables in there.

---

Procedure e will also not compile because you are not using the results of select * from newcustomer as cursor or store the results into variables.

---

EDIT:

Instead of procedures, you could use an anonymous block. Put the following into a file and execute it (via SQL*Plus for example):

Create Table newcustomer As Select * From customer;

Begin
  Null; --# Do something with your new table in here.
End;
/