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

Joe,

On a "total irresponsibility" scale of -10 to +10 with -10 being, say, Josef Stalin's handling of his little Soviet issues, and +10 being the creation of the World by God, where would you place this piece of code?

...inquiring minds ache to know.

-----Original Message-----
From: Joseph S. Testa [mailto:teci_at_oracle-dba.com] Sent: Tuesday, October 17, 2000 11:42 AM To: Multiple recipients of list ORACLE-L Subject: Re: [Q] Does ORACLE 8i support rename a column?

<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