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

depends on your perspective,

a production system -10, to learn, blowup and learn to perform incomplete recovery on a play database +10.

it has its uses, i hack on dictionary tables all the time on my 3 play databases on my desktop machine. But what I learn form meesign with tab$, col$, idx$, etc, i'd never do on a staging or production system.

my point is this, there are quite a few lurkers and newbies on this list, I wouldnt want to be the one responsible for putting code to the list like this and have someone(a newbie) think it is the norm, a kewl script and have them mess up their prod database.

There are lots of good scripts that pass thru this list and I wanted to make sure that the rename column one was NOT included in the "toolkit" you could use everyday.

A simple "be careful" is not enough to tell people, more a "this could entire hose up your database" would have been alot better warning.

joe
"Mohan, Ross" wrote:

> 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-----
> Sent: Tuesday, October 17, 2000 11:42 AM
> To: Multiple recipients of list ORACLE-L
>
> <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
Received on Tue Oct 17 2000 - 17:57:58 CDT