Alter table after keyword in Oracle

Question

ALTER TABLE testTable ADD column1 NUMBER(1) DEFAULT 0 NOT NULL AFTER column2;

Why can't I use mySql syntax in Oracle too? The above command works in MySql. Can you give me an equivalent that works?

Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"

I am asking if there is any way to use after clause in Oracle command that I provided?

are you asking for the corrent syntax or are you asking for the why? — cctan, Jan 19 '12 at 07:51
"Why can't I use mySql syntax in Oracle too?". As far as I know. MySQL has some very special SQL syntax that differs them from the rest. Almost all database vendors have syntax close to SQL standard but not implementing SQL standard. They are pretty much all different. — r4., Jan 19 '12 at 07:53
It works in MySql, not in Oracle or SqlServer; however, would love to have this feature — mijaved, Mar 12 '19 at 13:00

paxdiablo · Accepted Answer · 2012-01-19T08:28:55.107

18

Because SQL is a relational algebra. It doesn't care one bit about "where" columns are located within a table, only that they exist.

To get it to work in Oracle, just get rid of the after clause. The Oracle documentation for alter table is here but it boils down to:

alter table testTable
    add ( column1 number(1) default 0 not null )

There is no after clause for the alter table command.

edited Jan 19 '12 at 08:28

answered Jan 19 '12 at 07:53

paxdiablo

854,327
234
1,573
1,953

I want to be able to use that clause, without recreating the table – radu florescu Jan 19 '12 at 08:25
8

@Floradu88, then you need to talk to Oracle, not us. I'm not that close to Larry :-) – paxdiablo Jan 19 '12 at 08:28
@Floradu88: no, not without recreating the table. – paxdiablo Jan 19 '12 at 08:30
That is not an option, due to the fact that I cannot lose the data from the table. – radu florescu Jan 19 '12 at 08:31
Okay, I dropped the After in the command like you told me. But now I got this problem :http://stackoverflow.com/questions/8923091/ora-00600-when-running-alter-command – radu florescu Jan 19 '12 at 08:35
1

@Floradu88, you don't lose the data, you can create a new table, transferring the old data in to it (see the answer from Ilion for an example). You'll probably want to do it during downtime but it's doable. – paxdiablo Jan 19 '12 at 08:37
1

You can also use the DBMS_REDEFINITION package to reorder columns without taking it offline. – Adam Musch Jan 19 '12 at 16:26

Ilion · Answer 2 · 2012-01-19T08:05:18.320

Oracle does not support adding columns in the middle of a table, only adding them to the end. 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.

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 tan1New 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.

score 1 · Answer 3 · answered Jan 19 '12 at 07:57

1

Try this :

ALTER TABLE testTable ADD column1 NUMBER(1) DEFAULT 0 NOT NULL

answered Jan 19 '12 at 07:57

DrLazer

2,805
3
41
52

Alter table after keyword in Oracle

3 Answers3

Linked

Related