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

Gee.. my emails system is lagging behind. Just saw this email. I hope none of the newbie tried that codes at all.

I have to totally agree with Joe and Dick on this one. You don't mess around with the data dictionry tables like this. Or if you really wish to do it, update your resume first!

WInnie :)

dgoulet_at_vicr.com_at_fatcity.com on 10/17/2000 10:41:42 AM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

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

Joe,

    Completely agree. Magic (now defunct, wonder why?) did one of these on me
some time ago for one of their "products". The database shutdown nicely, but
startup was a mess. At least it provided a moments enjoyment to the individual
at OTS who I called. My night was ruined though.

Dick Goulet

____________________Reply Separator____________________
Author: "Joseph S. Testa" <teci_at_oracle-dba.com>
Date:       10/17/00 7:42 AM

<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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: dgoulet_at_vicr.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