Menu
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?
207
To MODIFY a column in an existing table, the Oracle ALTER TABLE syntax is: ALTER TABLE customers MODIFY column_name column_type; ALTER TABLE MODIFY allows modifying the constraints such as primary key, column size, type, its default value, length, nullability, etc. Here is an example of modifying the first_name column.
The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table.
ALTER TABLE is a DDL command, and all DDL command is "auto-committing", so you don't need to commit. ALTER TABLE is a DDL command, and as any DDL command is "auto-committing", so you don't need to commit.
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.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
