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

Answers


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;
/

Need Your Help

Zooming out with Selenium in Ruby

ruby selenium selenium-webdriver

Is it possible to zoom out in selenium with Ruby? I have searched the internet and most online answers are in Java, which seems to have some capabilities ruby doesnt.

Prevent upvote model from being called for every comment

ruby-on-rails ruby database performance model

I have three models: User, Comment and Upvote. User-to-Comment has a one-to-many relation, Comment-to-Upvote has a one-to-many relation and User-to-Upvote has a one-to-many relation.