Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] Does ORACLE 8i support rename a column?

Re: [Q] Does ORACLE 8i support rename a column?

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Tue, 17 Oct 2000 22:47:11 GMT
Message-Id: <10652.119557@fatcity.com>


I have to agree with Joe here. Yes, you CAN do this... but should you? Well, as my mother often told me "you can jump off the Empire State building but you will regret it when you are done".

I'm not sure Oracle will support your database if you go in and edit the underlying tables. Joe is right, you will screw up all references to that table if you do this. And yes, there is a LOT more to it than just editing the one table.

So... if you absolutely positively want to rename the column, why not create a view of the table that is identical to the table with that one column renamed? Create a public synonym on the view with the same name as the table and let people access it that way.

Geez, IT DOESN'T MATTER WHAT THE COLUMN NAME IS!!!!!!!!!!!!!!!!!!!!!




>From: "Joseph S. Testa" <teci_at_oracle-dba.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: [Q] Does ORACLE 8i support rename a column?
>Date: Tue, 17 Oct 2000 07:42:12 -0800
>
><soapbox mode on>
>
>Ok i consider this script totally irresponsible code(sorry to those who
>wrote it),
>
>Hopefully in this day of no one taking responsibility for their own
>actions,
>you dont get sued for some newbie running this script and then them coming
>back to haunt you for screwing up their data dictionary.
>
>Besides if i remember correctly, there was ALOT more to it than what this
>script provides. What happens to all of the procedures, etc that expects
>the column name, did they go invalid???
>
>Tinkering with the underlying $ tables is fine to experiement on your
>play/sand database but you better have you resume up to date if you decide
>to run this against a prod db.
>
><soapbox mode off>
>
>joe
>
>
>
>
>Grinalds wrote:
>
> > WAW! 8-O....
> > That was impressive!
> > May be someone have script that compare 2 schemas & generate
> > update script? ;0)
> >
> > > From: Juan Guillermo Rodriguez <jgrb16_at_tid.es>
> > > Date: Mon, 16 Oct 2000 22:02:50 +0200
> > > Subject: Re: [Q] Does ORACLE 8i support rename a column?
> >
> > > Not actually, but there is a trick ... Check this out:
> > >
> > > /*
> > > ** RenCol procedure ...
> > > ** RENames a table COLumn
> > > */
> > > Create Or Replace Procedure RenCol (
> > > 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
> > > obj_id number;
> > > col_id number;
> > > cursor_name1 INTEGER;
> > > cursor_name2 INTEGER;
> > > ret1 INTEGER;
> > > ret2 INTEGER;
> > >
> > > Begin
> > > Select object_id
> > > Into obj_id
> > > From dba_objects
> > > Where object_name=UPPER(table_name)
> > > And owner=UPPER(user)
> > > And object_type='TABLE';
> > >
> > > --DBMS_OutPut.put_line(obj_id);
> > >
> > > Select col#
> > > Into col_id
> > > From col$
> > > Where obj#=obj_id
> > > And name=UPPER(old_name);
> > >
> > > --DBMS_OutPut.put_line(col_id);
> > >
> > > Update col$
> > > Set name=UPPER(new_name)
> > > Where obj#=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;
> > > /
> > >
> > > Example of use:
> > > SQL> Create Table T ( C Number );
> > > SQL> Describe T;
> > > SQL> Exec PLX_RenCol( user, 't', 'c', 'new_c' );
> > > SQL> Describe T;
> > > SQL> Drop Table T;
> > >
> > > Hope it helps.
> > >
> > > PS: Be careful, since the previous code closely "touches" the data
> > > dictionary.
> >
> > Grinalds
> >
> > ========================================
> > A Randomly Selected Thought For The Day:
> > Random answers are my specialty! ;-)
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Grinalds
> > INET: g.geks_at_konts.lv
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Joseph S. Testa
> INET: teci_at_oracle-dba.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).



Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Received on Tue Oct 17 2000 - 17:47:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US