ORA-00904: invalid identifier

[amy85]

Hi another simple question ( sorry...)

When I try to create a table like this , I get the error below

 create table EMP (
        ID NUMBER not null,
        CREATION_DATE TIMESTAMP(3) not null,
        COMMENT VARCHAR2(255),
        primary key (ID)
    )
 
Error:
[B]ORA-00904:: invalid identifier[/B]
 SQL State: 42000
Error Code: 904

What am I missing?

Thanks again (... and again)

[tyro]

hey don't say sorry dear

you can't use COMMENT as your column name (the invalid identifier) because it is a oracle reserved term.
Use this

create table EMP (
        ID NUMBER not null,
        CREATION_DATE TIMESTAMP(3) not null,
        P_COMMENT VARCHAR2(255),
        primary key (ID)
    )

But if you must use COMMENT as a column name put it in between double quotes as below:

create table EMP (
        ID NUMBER not null,
        CREATION_DATE TIMESTAMP(3) not null,
        "COMMENT" VARCHAR2(255),
        primary key (ID)
    )

Cheers
[]

[rajavu]

As you might be knowing , it is not desirable to use the reserved words as a column name of a table . You have to use double quotes always in the application to refer the reserved key word column name. It is recommended to change the column name as suggested by Tyro.

SQL> CREATE TABLE EMP12 (
  2          ID NUMBER NOT NULL,
  3          CREATION_DATE TIMESTAMP(3) NOT NULL,
  4          "COMMENT" VARCHAR2(255),
  5          PRIMARY KEY (ID)
  6      )
  7  ;
 
 TABLE created.
 
 SQL> INSERT INTO  EMP12 VALUES ( 10, SYSTIMESTAMP, '1D=10');
 
 1 row created.
 
 SQL> SELECT ID ,COMMENT  FROM EMP12 ;
 SELECT ID ,COMMENT  FROM EMP12
           *
ERROR at line 1:
ORA-00936: missing expression
 
 
 SQL> SELECT ID ,"COMMENT"  FROM EMP12;
 
        ID COMMENT
 ---------- --------------------
        10 1D=10
 
 SQL>

[amy85]

hi guys thank you for your responses. I had initially used the double-quotes but now i have dropped that table and re-created with a different column name.