title: how to create dynamic table in oracle with dynamic column name and dynamic data type with out any views or any other table type
body: <p>Thanks for all,We can create a table dynamically with the help of <code>execute immediate</code> query. But when we create a table it is been created but if i wanted to create table dynamically with dynamic no of columns then question was raised. Actually I had created a table but when I created no of columns along with the table then a lots of errors raised. The following is the code which I had written in the Oracle in a procedure.</p>
  
  <pre><code>declare
      no_of_cols number:=&amp;no_of_cols;
      colname varchar2(20);
      coldata varchar2(20);
      i number;
  begin
      execute immediate 'create table smap1(nam varchar2(10))';
      age:='age';
      datf:='number'
      if(no_of_cols&gt;=2) then
          for i in 2..no_of_cols loop
              colname:=age;
              coldata:=datf;
              execute immediate 'alter table smapl add '||colname||' '||coldata;  
          end loop;
      end if;
  end;
  </code></pre>
  
  <p>then this code executing with four columns with same type if no_of_cols is 5.Then i had modified the code and run the plsql program.the program is as follows</p>
  
  <pre><code>declare
  no_of_cols number:=&amp;no_of_cols;
  colname varchar2(20);
  age varchar2(20);
  datf varchar2(20);
  coldata varchar2(20);
  i number;
  begin
      execute immediate 'create table smap1(nam varchar2(10))';
      if(no_of_cols&gt;=2) then
          for i in 2..no_of_cols loop
              age :=&amp;age;
              datf:=&amp;datf;
              colname:=age;
              coldata:=datf;
              execute immediate 'alter table smapl add '||colname||' '||coldata;  
          end loop;
      end if;
  end;
  </code></pre>
  
  <p>The following are the errors which are generated when the above procedure is created</p>
  
  <pre><code>  [Error] Execution (13: 19): ORA-06550: line 13, column 19:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
  
    ( - + case mod new not null &lt;an identifier&gt;
    &lt;a double-quoted delimited-identifier&gt; &lt;a bind variable&gt;
    continue avg count current exists max min prior sql stddev
    sum variance execute forall merge time timestamp interval
    date &lt;a string literal with character set specification&gt;
    &lt;a number&gt; &lt;a single-quoted SQL string&gt; pipe
    &lt;an alternatively-quoted string literal with character set specification&gt;
    &lt;an alternatively
  </code></pre>
  
  <p>i had done some modifications for the above plsql,then the plsql code will as follows</p>
  
  <pre><code>declare
  no_of_cols number:=&amp;no_of_cols;
  colname varchar2(20):='&amp;colname';
  coldata varchar2(20):='&amp;coldata';
  i number;
  begin
   execute immediate 'create table smap1(nam varchar2(10))';
  if(no_of_cols&gt;=2) then
  
      for i in 2..no_of_cols loop
         execute immediate 'alter table smapl add '||colname||' '||coldata;  
      end loop;
  end if;
  end;
  </code></pre>
  
  <p>then after executing i am getting the following error and it doenot read column name dynamically</p>
  
  <pre><code>[Error] Execution (1: 1): ORA-02263: need to specify the datatype for this column
   ORA-06512: at line 10
  </code></pre>