1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | SET SERVEROUTPUT ON ; DECLARE v_old VARCHAR2(20) := 'CARID' ; v_new VARCHAR2(20) := 'CAR_ID' ; v_sql VARCHAR2(200) := 'ALTER TABLE xxx RENAME COLUMN ' ||v_old|| ' TO ' ||v_new; v_exc VARCHAR2(200); CURSOR c1 IS SELECT b.table_name FROM user_tables a , user_tab_columns b WHERE a.table_name = b.table_name AND b.column_name = v_old ; BEGIN FOR c2 IN c1 LOOP BEGIN v_exc := REPLACE (v_sql, 'xxx' , c2.table_name); EXECUTE IMMEDIATE(v_exc); dbms_output.put_line(c2.table_name || ' SUCCESS' ); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(c2.table_name || ' FAIL' ); dbms_output.put_line(SQLCODE || ' ' || SQLERRM); END ; END LOOP; END ; / |