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

From: Juan Guillermo Rodriguez <jgrb16_at_tid.es>
Date: Mon, 16 Oct 2000 22:02:50 +0200
Message-Id: <10651.119379@fatcity.com>

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.

L wrote:

> Does ORACLE 8i support rename a column?
>
> If Yes, how to do it.
>
> Thanks.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: L
> INET: leed_at_chele.cais.net
>
> 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
Received on Mon Oct 16 2000 - 15:02:50 CDT