title: dynamic plsql procedure has some issues while executing it?
body: <p>Dynamic PL/SQL procedure has some problems. I had declared a procedure which will creates a table and columns dynamically on execution of that procedure. Actually the procedure has compiled with no errors, I had to take column name for every time but the values taken only once and shows an error that column already exists.</p>
  
  <pre><code>declare
  no_of_cols number:=&amp;no_of_cols;
  col_name varchar2(20):='&amp;col_name';
  query_state varchar2(2000);
  ind number:=2;
  begin
      query_state:='create table dupe(nam varchar2(10))';
      dbms_output.put_line(trim(replace(col_name,'%',' ')));
      execute immediate query_state;
      if(no_of_cols&gt;=2)then
          for ind in 2..no_of_cols loop
              execute immediate 'alter table dupe add '||(trim(replace(col_name,'%','  '))||ind)||' varchar2(20)';  
          end loop;
      end if;
  end;
  </code></pre>
  
  <p>Thanks in advance.</p>