problem with adding columns

[Unna]

Good morning all,

When altering a table to add new columns, these columns will be added to the end of the table. In our application, the ordering of the columns does matter and is important. Does anyone know how to add a column or columns in specific order or position within a table by using the "alter table add" clause, without recreating the table?


Thanks,
Unna

[Mr.Hanky]

As far as I know it is not possible, so you will either drop/re-create or re-code your apps.

MH

[pando]

re-code the app, if an app depends on that I must say it´s really s**t

I remember we had many ETL processes using SQL*Loader and the control files use column positions, everytime they made some schema change they had to recode 80% of ETL processes, pretty bad huh

[Mr.Hanky]

Originally posted by pando
re-code the app, if an app depends on that I must say it´s really s**t

I remember we had many ETL processes using SQL*Loader and the control files use column positions, everytime they made some schema change they had to recode 80% of ETL processes, pretty bad huh

Agreed, but sometimes sticking it to the app team is not what is necessarily best for "business". We have apps here that we inherited (black box) and they are usually sh*t but we have to deal with them.
Unfortunately we must always keep the big picture in mind.

MH

[pando]

if you inherit it then yes bad luck although you can still put pressure to providers to change them, we use a CRM here and everytime we need an index they say no-no but hey when we prove them the app goes better they make changes in their next version

And here Unna says "our application" I guess they developed it

[slimdave]

This might not be possible in your situation, but how about renaming the table with the column added to the end, and creating a view under the original name of the table to "reorder" the columns.

[Unna]

Thanks all for your help!

I did little search and here what I found,
. create a view, specify the order there
. recreate the table as select
. or create extra temp columns, populate them, and finally drop the unwanted ones. By the time of working out all these, it would have been quicker to just rebuild.


Thanks again!