SQL> DECLARE
  2    tabname varchar2(50);
  3    type rf is ref cursor;
  4    cur_tab rf; 
  5  begin
  6    Open cur_tab for 
  7      SELECT TABLE_NAME 
  8      FROM USER_TAB_COLUMNS 
  9      WHERE COLUMN_NAME = 'MODIFIEDBY' 
 10        AND DATA_LENGTH <> 100 
 11        AND UPPER(DATA_TYPE) <> 'NVARCHAR2';
 12    LOOP
 13      FETCH cur_tab into tabname;
 14      IF cur_tab%FOUND then
 15        EXECUTE IMMEDIATE 'ALTER TABLE '||TABNAME||' MODIFY MODIFIEDBY VARCHAR2(100)';
 16        INSERT INTO MODIFIEDTABLE(TABNAMES) VALUES(TABNAME);
 17      END IF;
 18      Exit When cur_tab%NOTFOUND;
 19    END LOOP;
 20    close cur_tab;
 21  end;
 22  /

PL/SQL procedure successfully completed.