0 
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

Improve this question
1
  • 1
    Why are you creating the table in a procedure? Why not create the table once, then do an ordinary INSERT AS SELECT? Or, if the table is only holding temporary data for a session, consider using a Global Temporary Table. Commented Apr 6, 2010 at 12:28

1 Answer 1

Reset to default
3

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;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

thanks for the response but is it possible to have exec createtables inside a procedure as I want all the procedures I have be executed through one entry point which is like my main procedure
yeah, I got I know the second one would not compile, it is only for demonstration.
The problem is not the execution of createTables, the problem is that your procedure e does not compile because it relies on a table that does not yet exist. What should that procedure do? Why can't you just query customer instead?
the e (main procedure) will be my entry point so when I execute this procedure , it should create a new table for me and then I will do a number of data manupulation in this procedure such as insert or delete. to ensure the procedure does not grow too large, I put the create table into new procedure which will be called by the main procedure. I think I really messed up the idea of procedure in pl/sql and other programming language. I am new to pl/sql
@christine33990: Please check if my updated answer helps you.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.