Oracle SQL Developer ORA-00904 on alter table

Question

I'm currently trying to alter an existing Oracle SQL table in Oracle SQL Developer.

I want to add a field to an existing table. The field should be not null, type NVARCHAR(256) and store a simple string - no foreign keys needed.

My SQL looks like this:

alter table MYTABLE
add column FRUITS NVARCHAR2(256) not null default 'apple';

When I run this sql statement, I get the following error:

Error starting at line : 1 in command -
alter table MYTABLE
add column FRUITS  NVARCHAR2(256) not null default 'apple'
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Do you know how to solve this error?

Answer 1

Wrong syntax as well as wrong order of constraints.

Should be

alter table MYTABLE
add FRUITS NVARCHAR2(256) default 'apple' not null;

---

Demonstration:

SQL> create table mytable (id number);

Table created.

SQL> alter table MYTABLE
  2  add column FRUITS NVARCHAR2(256) not null default 'apple';
add column FRUITS NVARCHAR2(256) not null default 'apple'
    *
ERROR at line 2:
ORA-00904: : invalid identifier


SQL> alter table MYTABLE
  2  add FRUITS NVARCHAR2(256) not null default 'apple';
add FRUITS NVARCHAR2(256) not null default 'apple'
                                           *
ERROR at line 2:
ORA-30649: missing DIRECTORY keyword


SQL> alter table MYTABLE
  2  add FRUITS NVARCHAR2(256) default 'apple' not null;

Table altered.

SQL>