Re: How to rename a column name?

From: <wy3141_at_my-deja.com>
Date: Wed, 15 Sep 1999 12:45:36 GMT
Message-ID: <7ro4dc$aph$1@nnrp1.deja.com>

I get the ways from other forum(www.oraclefans.com).

Tip of the Week May 28, 1999 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.) asid 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=3DUPPER(table_name)
        and owner=3DUPPER(user) and object_type=3D'TABLE';
        select  col# into col_id from col$ where obj#=3Did and
                name=3DUPPER(old_name);        dbms_output.put_line
(col_id);
        update col$ set name=3DUPPER(new_name)
        where obj#=3Did and col#=3Dcol_id;        commit;
      cursor_name1 :=3D DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cursor_name1, 'ALTER SYSTEM FLUSH
                        SHARED_POOL',DBMS_SQL.native);
      ret1 :=3D DBMS_SQL.EXECUTE(cursor_name1);
      DBMS_SQL.CLOSE_CURSOR(cursor_name1);
      cursor_name2:=3D DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cursor_name2, 'ALTER SYSTEM
                        CHECKPOINT',DBMS_SQL.native);
      ret2:=3D DBMS_SQL.EXECUTE(cursor_name2);
      DBMS_SQL.CLOSE_CURSOR(cursor_name2);

end;
It works very well!

In article <7rimke$e4a$1_at_nnrp1.deja.com>,   wy3141_at_my-deja.com wrote:
> I want to change the column name,and I don't want to drop it and
> recreate it. Because it has some dependencies(role right,constraints)
> on it.
> Can Oracle support change the column name directly?
> Any suggestion are helpful.
> Thanks in advance.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 15 1999 - 07:45:36 CDT