DECLARE
  COUNTER NUMBER(2) := 0;
  COL_NAME_OLD VARCHAR2(255);
BEGIN
  FOR x IN (select TABLE_NAME, COLUMN_NAME from ALL_TAB_COLUMNS where OWNER='<REPORTING SCHEMA NAME>' AND DATA_TYPE like 'BINARY%')
  LOOP   
    COL_NAME_OLD := 'COL_' || COUNTER || '_OLD';
    DBMS_OUTPUT.PUT_LINE('RENAMING '|| x.TABLE_NAME || ' COLUMN FROM '|| x.COLUMN_NAME || ' to ' || COL_NAME_OLD ||' ' || COUNTER || '');
    EXECUTE IMMEDIATE 'ALTER TABLE '|| x.TABLE_NAME || ' RENAME COLUMN '|| x.COLUMN_NAME || ' to ' || COL_NAME_OLD ||'';
    EXECUTE IMMEDIATE 'ALTER TABLE '|| x.TABLE_NAME || ' ADD '|| x.COLUMN_NAME || ' NUMBER(14,4)';
    EXECUTE IMMEDIATE 'UPDATE '|| x.TABLE_NAME || ' SET '|| x.COLUMN_NAME || ' = ' || COL_NAME_OLD || '';
    EXECUTE IMMEDIATE 'ALTER TABLE '|| x.TABLE_NAME || ' DROP COLUMN '|| COL_NAME_OLD || '';
    COUNTER := COUNTER + 1;
  END LOOP;
END;