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

Ditto. I strongly agree with Joe's sentiments on this.

I have tried this in the past. It may or may not work.

An Oracle instructor I know tried this once. It blew up his database and it could not be repaired.

Jared

On Tue, 17 Oct 2000, Joseph S. Testa wrote:

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

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Received on Tue Oct 17 2000 - 11:54:41 CDT