title: Execute immediate 'alter table' error pl/sql
body: <p>I have a table and I want to create a copy of it. But I want this copy to be dynamic. First table may change. In a FOR LOOP, for each i (except 1), I want to alter the second table and add a column with the name "Col"+"i". Example: Col1, Col2, Col3.
  While debugging, everything is ok except this line: <code>EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' ADD Col'||i||' NUMBER(2)';</code> which throws 2 errors: "ORA-01403: no data found" and "ORA-06512"
  What am I doing wrong? Thank you!</p>
  
  <pre><code>CREATE TABLE first_table (
  id NUMBER(2,0) NOT NULL PRIMARY KEY,
  col1 NUMBER(2,0) NOT NULL,
  col2 NUMBER(2,0) NOT NULL,
  col3 NUMBER(2,0) NOT NULL,
  col4 NUMBER(2,0) NOT NULL,
  col5 NUMBER(2,0) NOT NULL,
  col6 NUMBER(2,0) NOT NULL,
  col7 NUMBER(2,0) NOT NULL
  );
  
  CREATE TABLE second_table (
  first_col NUMBER PRIMARY KEY
  );
  
  CREATE OR REPLACE PROCEDURE second_table
  AS
  nr_columns NUMBER(2);
  tab_name VARCHAR2(20);
  col_nam VARCHAR2(20) := 'COL';
  BEGIN
  
  SELECT COUNT (*)
  INTO nr_columns
  FROM user_tab_columns 
  WHERE table_name='FIRST_TABLE';
  
  SELECT table_name
  INTO tab_name
  FROM user_tab_columns
  WHERE column_name ='FIRST_COL';
  
  FOR i IN 1..NR_COLUMNS
  LOOP
  
  IF (i=1) THEN
  EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' RENAME COLUMN FIRST_COL TO id';
  ELSE
  EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' ADD Col'||i||' NUMBER(2)';
  
  END IF;
  END LOOP;
  END;
  /
  </code></pre>