Database: Oracle Add new column but not the last column (sequence)?

Question

One question

Database: Oracle

This is the stmt (Desc.: chnage type VARCHAR to CLOB)

ALTER TABLE XX
 ADD (TEMP_Value CLOB);
UPDATE XX SET TEMP_Value=Value;
COMMIT;
ALTER TABLE XX DROP COLUMN Value;
ALTER TABLE XX
RENAME COLUMN TEMP_Value TO Value;

the problem: The new clob-column is the last column in the XX table (normally). If value second was now is the last column, How to change the sequence

The order of columns in a table is totally irrelevant (except for `LONG` columns). If you need them in a specific order, just put them into your `SELECT` statement in that order — , Oct 14 '13 at 12:03
@a_horse_with_no_name +1 - there is NO meaning to the order of the columns in a database table like you have, there is no real need to try to put them in order. — jim mcnamara, Oct 14 '13 at 12:08

score 0 · Answer 1 · answered Oct 14 '13 at 12:07

0

I know the following solution and that is not very smart for several of columns, so I want to find a other solution.

create newtable as
select Value, X, XX,..

drop table XX;
rename newtable to XX;

answered Oct 14 '13 at 12:07

Fawi

473
2
7
21

score 0 · Answer 2 · edited May 23 '17 at 11:57

Discussed here

Oracle does not support adding columns in the middle of a table, only adding them to the end, unlike MYSQL ALTER TABLE TABLENAME ADD COL1 AFTER COL2 command. Your database design and app functionality should not depend on the order of columns in the database schema. You can always specify an order in your select statement, after all, which would be best practice.

SELECT * FROM TABLE is not a good practice.

However if for some reason you simply must have a new column in the middle of your table there is a work around.

CREATE TABLE TAB1NEW
AS
    SELECT
          0 AS COL1,
          COL1 AS COL2
    FROM
          TAB1;

DROP TABLE TAB1 PURGE;

RENAME TAB1NEW TO TAB1;

Where the SELECT 0 AS col1 is your new column and then you specify other columns as needed from your original table. Put the SELECT 0 AS col1 at the appropriate place in the order you want.

Afterwards you may want to run an alter table statement on the column to make sure it's the data type you desire. Remember to put back your constraints, indexes, partition... and whatever as per the original table

Database: Oracle Add new column but not the last column (sequence)?

2 Answers2