Re: How to rename a column name?

A copy of this was sent to wy3141_at_my-deja.com (if that email address didn't require changing) On Wed, 15 Sep 1999 12:45:36 GMT, you wrote:

BE VERY VERY CAREFUL USING THIS OR ANYTHING THAT UPDATES THE DATA DICTIONARY. If you have procedures to that refer to the table in question -- be prepared for spurious internal errors.

If you want your database supported -- don't do this.

If you are running a production system -- don't even think about it.

Maybe, just maybe, if you have a development database that you can totally lose and rebuild from scratch, you can think about using this (although why you wouldn't rename the table and create a view with the "correct" name is beyond me).

>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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 15 1999 - 09:03:22 CDT