Re: How to rename a column name?

As per my other post, isn't this what the OEM Change Management Pack does?

Thanks,

Yosi

Thomas Kyte wrote:

> 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 Fri Sep 17 1999 - 11:30:43 CDT