In Oracle, is it possible to "insert" a column into a table?

Question

When adding a column to an existing table, Oracle always puts the column at the end of the table. Is it possible to tell Oracle where it should appear in the table? If so, how?

Answer 1

Why does the order of the columns matter? You can always alter it in your select statement?

There's an advantage to adding new columns at the end of the table. If there's code that naively does a "SELECT *" and then parses the fields in order, you won't be breaking old code by adding new columns at the end. If you add new columns in the middle of the table, then old code may be broken.

At one job, I had a DBA who was super-anal about "Never do 'SELECT *'". He insisted that you always write out the specific fields.

Answer 2

rename YOUR_ORIGINAL_TABLE as YOUR_NEW_TABLE;

create table YOUR_ORIGINAL_TABLE nologging /* or unrecoverable */ as select Column1, Column2, NEW_COLUMN, Column3 from YOUR_NEW_TABLE;

Drop table YOUR_NEW_TABLE;

Select * From YOUR_ORIGINAL_TABLE; <<<<< now you will see the new column in the middle of the table.

But why would you want to do it? It's seems illogical. You should never assume column ordering and just use named column list if column order is important.