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

From: Hamid Alavi <HamidA_at_aimdirect.co.nz>
Date: Wed, 18 Oct 2000 09:23:38 +1300
Message-Id: <10652.119543@fatcity.com>

You can use TOAD for this purpose, compare 2 schema...with diff sort of objects

-----Original Message-----
From: Grinalds [mailto:g.geks_at_konts.lv]
Sent: Wednesday, 18 October 2000 01:28
To: Multiple recipients of list ORACLE-L Subject: 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

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

Received on Tue Oct 17 2000 - 15:23:38 CDT