Re: Beginner - Update table

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0212060625.7bc44ef6_at_posting.google.com>...
> "Mark Muller" <mmu_at_beer.com> wrote in message news:<aspm6a$mvj$1_at_news.hccnet.nl>...
> > How to write a sql script that whill alter an existing table to a new
> > definition
> >
> > I have to modify tables, i know what the columns should be, but not what
> > they were (depending on last change at customer site),
> > for example the following table where MOB is a new column. When i try this
> > oracle (9i) is complaining the column ID already is NOT NULL and does not
> > add the MOB column.
> >
> > ALTER TABLE vaccin.HUISARTS MODIFY (
> > ID CHAR(10) not null,
> > NAME CHAR(30) not null,
> > TEL CHAR(15) null,
> > FAX CHAR(15) null,
> > MOB CHAR(15) NULL
> > );
>
> Mark, you ADD new columns and MODIFY existing columns. IF MOB doesn't
> exist your are using the wrong command. If it does exist then in a
> case where you do not know what the table looks like at the customer
> site it might be wise to perform each column modification in a
> separate alter command because if any of the column statements fail
> the whole statement fails as written.
>
> You might also want to consider changing your char columns to
> varchar2, but that is besides the point of your post.
>
> IMHO -- Mark D Powell --

Another approach might be to spool out a describe of the table, then spool a select all the fields of the table, drop the table, add the new table, massage the describe into an insert using the existing values.

It isn't really a beginner exercise to handle all possibilities at any site your script might hit without potential manual intervention. If you don't have to save the existing data in the table, that would make it much easier.

Mark's way of having a bunch of independent statements is good, but customers trying to figure out which errors to ignore generally leads to ignoring all errors, which may not be so good. Also, long ago on a version far, far away, I've seen the database writer hickup and miss a DDL in the middle of a string of DDL's.

jg

--
@home is bogus.
Of course, you could just tell them to see their DBA.  :-)