I am using Oracle. When I define the new tables, I need to add one column
at a time (or a few columns at a time for large table) using 'alter table
add column_name...'. Is there any way I can modify the ordering of the
columns using the 'alter table' command? Because later on, I have some old
scripts using insert which rely on pre-defined column ordering in table
definition:

insert into table_name
values (...);
-- without explicit column names specified which means the ordering of
columns is important

Thx for advising

If you had properly written your insert statements to begin with i.e.
insert into blah (all columns) values (all values); then you would not
be in this predicament.

Even though you can "re-arrange" the ordering of the columns by going
thru few steps.... I would suggest you correct your old scripts and
include column names in the insert statements. This will be a better
solution in the long run.

Regards
/Rauf

In article <41c69a0b$1_2@rain.i-cable.com>, <tncc> says...
>
>I am using Oracle. When I define the new tables, I need to add one column
>at a time (or a few columns at a time for large table) using 'alter table
>add column_name...'. Is there any way I can modify the ordering of the
>columns using the 'alter table' command? Because later on, I have some old
>scripts using insert which rely on pre-defined column ordering in table
>definition:
>
>insert into table_name
>values (...);
>-- without explicit column names specified which means the ordering of
>columns is important
>
>Thx for advising
>
>
>

as the other person pointed out -- relying on the order of columns like that is
hugely bad. It would be preferable to fix the scripts.

But, you can use views

rename table T to t_table;

create view T as select <columns in whatever order you like> from t_table;

alter table t_table add foobar number;

create or replace view T as select <columns in whatever order you like> from
t_table;


--
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation