Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 356
jalal rasooly Avatar asked Jan 20 '14 11:01

jalal rasooly


1 Answers

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;
like image 182
pahariayogi Avatar answered Sep 22 '22 19:09

pahariayogi