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

This is a multi-part message in MIME format.

--------------BAA7AF71200749A7EED1092A
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi, Grinalds.

Maybe you are interested in LANG_UPDTAB package, from Mark Lang. It is available for download at http://www.geocities.com/SiliconValley/Lakes/1261

I send you the README file, as attachment, with a brief description of the utility.

Hope it helps,
Juan Guillermo.

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! ;-)

--
----------------------------------------
Juan Guillermo Rodríguez
Consultor Principal

Oracle Consulting Services
Oracle Ibérica, S.R.L.
Navaluenga, 1 y 3
28230 Las Rozas
Madrid
España

Oracle:
+ Centralita: 916312000
+ Directo:    916312069
+ Fax:        916312189
+ e-mail: JuanGuillermo.Rodriguez_at_oracle.com
+ http://www.oracle.com/

In situ (Telefónica I+D):
+ Directo:    913374936
+ e-mail: jgrb16_at_tid.es

Personal:
+ Móvil:      609588941
----------------------------------------
DISCLAIMER: The statements and opinions expressed here are my own and do not
necessarily represent those of Oracle Corporation.

RENUNCIA: Las opiniones e ideas expresadas son propias y no necesariamente
representan las de Oracle Corporation.
----------------------------------------


--------------BAA7AF71200749A7EED1092A
Content-Type: text/plain; charset=iso-8859-1;
 name="langutab.txt"
Content-Transfer-Encoding: 8bit
Content-Disposition: inline;
 filename="langutab.txt"

LANG_UPDTAB Package, 23-Mar-98
Copyright © 1998, Mark Lang
(Requires Oracle 7.3,8.0 or greater)


LANG_UPDTAB is a package that contains one procedure: UPDATE_TABLE.
This extremely useful procedure is a generic utility to copy data
from one table to another, much like the SQL*Plus COPY command, with
some important differences:

        Table structures do not have to be identical
        Can insert new rows and update existing rows, and even update
          only those rows that have changed
        Compare tables by row and/or column
        Exclude specific columns not to insert and/or update
        Warns you of differences in column type, size, nullness
        Print execution report of all actions
        Allow set COMMITPOINT for large loads
        Works with remote tables
        Reports progress via DBMS_APPLICATION_INFO
        ...and much, much more

Here is a simple example which copies rows from OLD_EMPLOYEES
into EMPLOYEES which do not already exist:

        lang_updtab.update_table(
        dtab=>'EMPLOYEES'
        , stab=>'EMPLOYEES_STAGE'
        , flags=>'I'
        );

        The "I" flag tells UPDATE_TABLE to insert NEW rows from
        OLD_EMPLOYEES into the EMPLOYEE table.

        You could easily modify the behavior of the update by
        adding some other common flags (there are 20 available)

        U=update existing rows with same primary key (determined
          by procedure; you can also override)
        C=update only those existing rows which have changed
          (reduces unnecessary redo)
        R=work in REPORT-ONLY mode (no changes made)

        L=SPOOL generated code to DBMS_OUTPUT!!!
        J=submit as a job

        E=echo errors to screen
        W=show warning of column differences in type, size, nullness

        There are also 19 PARAMETERS available (including the
        3 above: STAB, DTAB, and FLAGS) to further customize
        behavior.


All this can be done with a single procedure call.  UPDATE_TABLE
dynamically generates and executes an anonymous PL/SQL routine
to perform the update based on your parameters.  You can even
have UPDATE_TABLE spool the code it generates to DBMS_OUTPUT
where you can view or modify it.

This procedure has a variety of applications for DBA's and
developers.  Anyone who has ever had to write a large INSERT INTO
(...) SELECT ... FROM, or similar UPDATE statement will know
what I'm talking about.  Below lists just some of the most common
uses:

        Move / copy data when...
          Table structures are different
          Want to update existing rows
          Need to load large amounts of data (COMMITPOINT)
          Take advantage of any other UPDATE_TABLE features
        Load external data via stage tables loaded by SQL*Loader
        Compare data in two tables by row or column
        Support drop_column and other table maintenance operations
        Refresh TEST / DEVELOPMENT databases
        Maintain read-only, primary-key "snapshots"
        Use to generate template code for more complex updates

UPDATE_TABLE is completely dynamic and generic.  You may set
up to 19 parameters and 20 flags to customize the behavior of the
procedure, making it extremely flexible and usuable in a great
deal of situations.  It also contains complete (13+ pages in
Micorsoft Word 95 or HTML formats) documentation and all the
source code.  Hopefully, future versions of the package will
contain other helpful routines for managing table data.