Adding a column to oracle table

[arobinson98]

Greetings,
Is there a way to add a column to an oracle table someplace other than the end of the list? Ie. if I have a table that had columns - Name, Address, Phone and I want to add a state and zip column but for readability want to add them after address column - Name, Address, State, Zip, Phone...how could I do that without dropping the table and recreating the whole thing (Oracle 7.3). In MySql I believe you can ALTER TABLE tablename ADD columnname AFTER columnname but I can't find and equivelant in Oracle. The ALTER command doesn't appear to support AFTER nor have a option to re-order the columns. Thanks.

[stuka]

Why does it matter WHERE the DB puts the column? If you want your results ordered differently, just do your query in a particular order.

[arobinson98]

It doesn't matter really programatically, there are 2 "reasons" why, one is mostly readability in the documentation, the other is referencing columns positionally in existing code. If your code reads order by 1, 2, 3 and the column you're adding is what you want in position 3 for the order by and hte table has 6 columns in it you'll want to add the column after column 2 unless you want to change the code.

[stuka]

Call me ornery, but wouldn't changing the 1,2,3 to column names be a Good Thing(TM) in general? This way changes (short of column name changes) in the table don't affect you - making the code far more maintainable.

[arobinson98]

You are absolutely right, and that's not being ornary, it's being intelligent. However this is not new development, nor do I have the luxury to spend the time doing analysis and changing existing code outside of my project specs. This is a large, old system with a lot of existing code "standards" and consistancies that I can't change and since a plethora of programmers have had their hand on it the logic does get almost gruesome in some places. At the moment my task is to write a little .sql script that'll add a column in the middle of a table on a development database and someone will run that script on the parallel test and production databases. Any additional code I change is outside the scope of my project specifications, I'll need approval and then make the appropriate changes in 3 different areas. Sometimes the best way to do something is not always what you are able to do. On development I created the appropriate table by doing a create table temp as (select 1, 3 , 2 from original), then dropped the original and recreated it as a copy of the temp but then I needed to recreate all the indexes as well, and this seemed like there was too much room for error when working with a production database so if there's a wat to simply add a column to the middle that would be easier. I'm not trying to defend the reasoning, just get the job done. Thanks. :)

[stuka]

Hey, no problem - we all understand job pressures and working with existing stuff (or will eventually ;) ). As for actually knowing how to do it, sorry I can't help there!

[DNAunion2000]

/*DNAunion*/ If all else fails, what about the following: you could SELECT all columns from the table, but in the order YOU want, saving the result in a new table. Then, drop the original table from the database (you might want to document all of the constraints and rights etc. first so that you can recreate them as the last step) and then rename/add the new one: finally, reestablish all of the constraints etc.

[barbers]

A bit late, but for what it's worth, inserting new field names between
other fields in a table in a legacy system could be disatrous if past
developers have used ordinal positions instead of field names in
referencing their query results where the asterisk was used in the
Select statement.

For instance. . .

Table: Employees
Fields: FN, LN, Title

sqlstatement = "Select * From Employees "
Set rs = db.OpenRecordset(sqlstatement)

rs(0) = FN, rs(1) = LN, rs(2) = Title

Now, say we add Address, City, State before Title

Table: Employees
Fields: FN, LN, Address, City, State, Title

The recordset now gives the following:

rs(0) = FN, rs(1) = LN, rs(2) = Address