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

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