Oracle question: execute immediate

Question

I want to create table like this:

create table ttt
(
   col1 varchar2(2),
   col2 varchar2(2),
   col3 varchar2(2),
   col4 varchar2(2),
   col5 varchar2(2)
);

with this procedure, but it does not work. May you help me?

declare
  str varchar2(200);
  i int;
begin
  for i in 1 .. 5 loop
  begin
    str:=’str’||i||”;
  end;
  end loop;
  execute immediate ‘create table t1 (“str” varchar2(2) )’;
end;
/

Thanks in advance.

Answer 1

I believe you want something like

declare
  str varchar2(200);
  i int;
begin
  for i in 1 .. 5 loop

    str:= str || 'col' || i || ' varchar2(2)';
    if i < 5 then
       str := str || ',';
    end if;

  end loop;
  execute immediate 'create table t1 (' || str || ')';
end;
/

But of course, another interesting question is why you want to create a dynamic table. Maybe a global temporary table can do what you need?