Re: How do DBA products manage to insert a column in a table ??

From: Michael Rothwell <michael_rothwell_at_non-hp-usa-om46.om.hp.com>
Date: 1998/02/26
Message-ID: <34F5BAEF.1CBA203E@non-hp-usa-om46.om.hp.com>#1/1

OK,
Here is the script that inserts a new column into a table given a table INSIGHT.MYTEST with the following structure and inserting a new column NEWCOL between COL2 and COL3.

    COL1   VARCHAR2(20)     NULL,
    COL2   VARCHAR2(20)     NULL,
    COL3   VARCHAR2(20)     NULL,
    COL4   VARCHAR2(20)     NULL

RENAME MYTEST TO MYTEST_02261998185225000 ;
CREATE TABLE INSIGHT.MYTEST
(

    COL1   VARCHAR2(20)     NULL,
    COL2   VARCHAR2(20)     NULL,
    NEWCOL VARCHAR2(2)      NULL,
    COL3   VARCHAR2(20)     NULL,
    COL4   VARCHAR2(20)     NULL

)
;
INSERT INTO INSIGHT.MYTEST(

                           COL1,
                           COL2,
                           NEWCOL,
                           COL3,
                           COL4
                          )
                    SELECT 
                           COL1,
                           COL2,
                           NULL,
                           COL3,
                           COL4
                      FROM INSIGHT.MYTEST_02261998185225000

;
COMMIT
;

and that's all there is to it. I have to say that DBArtisan from Embarcadero is best DBA tool I've seen yet.

MR.

Jerry Gitomer wrote:
>
> Hi Joe,
>
> Do you think that they rename the table, add the column at the end,
> create a view in the desired order and give it the name of the original
> table :-)
>
> Regards
>
> Jerry
>
> joseph_testa_at_aep.com wrote:
> >
> > To add a new column is no biggie, alter table <tablename> add column_name
> > column_type
> >
> > now oracle's stand on adding a column to the middle is that the order of
> > columns in a table makes no difference except in the case of (raw) long
> > datatype where you want it at the end.
> >
> > Adding a table to a column is a new one on me.
> >
> > To drop a column is alot harder, but I've written a package that will do it,
> > you can find it on www.oracle-dba.com
> >
> > joe
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
> --
> Jerry Gitomer ICT Group
> jgitomer_at_ictgroup.com Langhorne PA
> jgitomer_at_p3.net Opinions are mine not those of ICT Group
Received on Thu Feb 26 1998 - 00:00:00 CST