How to create a table inside of a procedure in Oracle?

Question

I want to create a table inside of a procedure. I tried to put the create query in a string then execute immediate the string. For example:

create or replace procedure hr.temp is
   var1 varchar2(4000);
begin
   var1:='create table hr.temp(
          id number)';
   execute immediate var1;
end temp;

But when I execute this procedure I get the error:

ORA-00911: invalid character
ORA-06512: at "SYS.TEMP", line 6
.
.
.

Is there any way I can do this?

Do NOT create procedures in the SYS account. Don't do it. Ever. (Once you move that procedure to a proper account: inside a dynamic SQL you must not specify the ; character. Execute immediate can execute only a single statement anyway, so no termination character is required) — a_horse_with_no_name
due to your suggestion i changed it to hr schema,but the result is the same — jalal rasooly
i removed the ; character,now when i execute the procedure,it begin but never end,it's like procedure is in the loop that never end! — jalal rasooly
Your example still includes the ;. Btw: you can't create a table with the same name as an existing procedure. But apart from that this should work fine: sqlfiddle.com/#!4/37847/2 — a_horse_with_no_name
@jalalrasooly The name of procedure and table should be distinct, not the same. Did you change both to temp2? — Yaroslav Shabalin

pahariayogi pahariayogi · Accepted Answer · 2014-01-22T07:15:16

Try this. It should work...

create or replace procedure hr.temp is
   var1 varchar2(4000);
BEGIN
   var1:='create table hr.temp2(
          id number)';
   EXECUTE IMMEDIATE var1;
end temp;

How to create a table inside of a procedure in Oracle?

2 Answers