1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00904: invalid identifier

Discussion in 'SQL PL/SQL' started by amy85, Apr 4, 2009.

  1. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi another simple question ( :) sorry...)

    When I try to create a table like this , I get the error below
    Code (Text):


     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)
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    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
    Code (Text):

    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:
    Code (Text):

    create table EMP (
            ID NUMBER not null,
            CREATION_DATE TIMESTAMP(3) not null,
            "COMMENT" VARCHAR2(255),
            primary key (ID)
        )
     
    Cheers :)
    []
     
    amy85 likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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.

    Code (SQL):

    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 likes this.
  4. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    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.