3

I am an SQL rookie and I would very much appreciate some assistance on this rather basic issue.

alter table OCEAN_ANTENNE_TEMP
add column ANT_TILT_M number(5) not null,
ANT_FSC_ANT number(4,1) default 0;
/

why is this query giving me this error:

SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier"

Jacob
  • 14,463
  • 65
  • 207
  • 320
Steffi
  • 31
  • 1
  • 1
  • 3
  • see the updated answer..not null should be used with default..else an exception is thrown when alter is excecuted! – vhadalgi Mar 05 '14 at 11:57
  • You don't need the `;` **and** the `/` See here for details: http://stackoverflow.com/q/1079949/330315 –  Mar 05 '14 at 12:32

2 Answers2

2

correct method is

alter table OCEAN_ANTENNE_TEMP 
add ( ANT_TILT_M number(5) not null, 
ANT_FSC_ANT number(4,1) default 0);
vhadalgi
  • 7,027
  • 6
  • 38
  • 67
simplify_life
  • 405
  • 4
  • 18
2
alter table OCEAN_ANTENNE_TEMP 
add (ANT_TILT_M number(5) default 0 not null, 
      ANT_FSC_ANT number(4,1) default 0 not null);

See here for correct syntax

See here for Documentation

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • it still gives me an error: Error report: SQL Error: ORA-01758: table must be empty to add mandatory (NOT NULL) column 01758. 00000 - "table must be empty to add mandatory (NOT NULL) column" – Steffi Mar 05 '14 at 11:44
  • 3
    @user3383166 The error message is pretty self-explanatory - you cannot add a NOT NULL column to a non-empty table (think about it - how is Oracle supposed to fill this column?). You should add the column without NOT NULL, fill it, and create the NOT NULL constraint afterwards. – Frank Schmitt Mar 05 '14 at 11:46
  • 1
    `ALTER TABLE ADD COLUMN statement. However, a column with a NOT NULL constraint can be added to an existing table if you give a default value; otherwise, an exception is thrown when the ALTER TABLE statement is executed.` – vhadalgi Mar 05 '14 at 11:49
  • 1
    The second link is for MySQL. It's ***not*** for Oracle. –  Dec 31 '14 at 12:32