Dynamic SQL not working as expected
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
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.
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; /