Title: Renaming a Table Column

This Code of the Week entry comes from Ranjay Dhiman, an Oracle database
      administrator for CMC Limited., in New Delhi, New Delhi, India.

      This is a procedure to rename a column name, replacing the cumbersome process of
      renaming the column where new table with new name has to created and then
      populating the table with existing data.

      This process takes the owner name, table name, old name of the column of the table to
      be renamed and new name of the procedure as its arguments. This procedure should be
      run under SYS user.

      Create or replace procedure chg_colnm(

      user in varchar2,   -- name of the schema.

      table_name in varchar2,  -- name of the table.

      old_name in varchar2,    -- name of the column to be renamed.

      new_name in varchar2    -- new name of the column.
      ) as
      id number;
      col_id number;
      cursor_name1 INTEGER;
      cursor_name2 INTEGER;
      ret1 INTEGER;
      ret2 INTEGER;
      begin
              select object_id into id from dba_objects where
                      object_name=UPPER(table_name)
              and owner=UPPER(user) and object_type='TABLE';
              select  col# into col_id from col$ where obj#=id and
                      name=UPPER(old_name);
              dbms_output.put_line(col_id);
              update col$ set name=UPPER(new_name)
              where obj#=id and col#=col_id;
              commit;
            cursor_name1 := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(cursor_name1, 'ALTER SYSTEM FLUSH
                              SHARED_POOL',DBMS_SQL.native);

            ret1 := DBMS_SQL.EXECUTE(cursor_name1);
            DBMS_SQL.CLOSE_CURSOR(cursor_name1);
            cursor_name2:= DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(cursor_name2, 'ALTER SYSTEM
                              CHECKPOINT',DBMS_SQL.native);
            ret2:= DBMS_SQL.EXECUTE(cursor_name2);
            DBMS_SQL.CLOSE_CURSOR(cursor_name2);
      end;
      /