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 Cushgod, Nov 15, 2009.

  1. Cushgod

    Cushgod Guest

    Hi my name is Anthony and I am taking Database I course.

    create table DonationEvent
    ( "EventID" NUMBER(1,1) Not Null,
    "EventNumber" NUMBER(1,1) Not Null,
    "Type" Char(30) Not Null,
    "OrganizerName" Char(30) Not Null,
    "Location" Char(30) Not Null,
    "Date" NUMBER(10,1) Not Null,
    "Duration" Char(30) Null,
    "DonationTotal" NUMBER(10,1) Not Null,

    Primary Key (EventID),

    );

    ORA-00904: : invalid identifier :mad:


    Please Help!! Thank you in advance! Also in Oracle Express Edition 10g why cant I see what line this error is happening on?:eek:
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Invalid Identifier for newb (me)

    You have several errors; let's look at a corrected statement:

    Code (SQL):
    CREATE TABLE  DonationEvent
       (     EventID           NUMBER    NOT NULL,
             EventNumber    NUMBER    NOT NULL,
             EventType       CHAR(30)       NOT NULL,
             OrganizerName  CHAR(30)       NOT NULL,
             Location          CHAR(30)       NOT NULL,
             EventDate       NUMBER(10,1)   NOT NULL,
             Duration          CHAR(30)       NULL,
             DonationTotal   NUMBER(10,1)   NOT NULL,
             CONSTRAINT donationevent_pk
             PRIMARY KEY (EventID)
    );
     
    Type and Date are reserved words so you cannot use them as column names. NUMBER(1,1) creates a column to store a 1 decimal place number with no integer component; I think you don't wannt that. You can't declare a constraint at the end of the column list without specifying the CONSTRAINT keyword and a name.

    Study the corrected statement to see how to write such commands.
     
  3. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    create table DonationEvent
    ( "EventID" NUMBER(1,1) Not Null,
    "EventNumber" NUMBER(1,1) Not Null,
    "Type" Char(30) Not Null,
    "OrganizerName" Char(30) Not Null,
    "Location" Char(30) Not Null,
    "Date" NUMBER(10,1) Not Null,
    "Duration" Char(30) Null,
    "DonationTotal" NUMBER(10,1) Not Null,

    Primary Key ("EventID")

    );
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, the statement works if you set sqlblanklines on; by default it throws the following error:

    Code (SQL):
     
    SQL> CREATE TABLE DonationEvent
    2 ( "EventID" NUMBER(1,1) NOT NULL,
    3 "EventNumber" NUMBER(1,1) NOT NULL,
    4 "Type" CHAR(30) NOT NULL,
    5 "OrganizerName" CHAR(30) NOT NULL,
    6 "Location" CHAR(30) NOT NULL,
    7 "Date" NUMBER(10,1) NOT NULL,
    8 "Duration" CHAR(30) NULL,
    9 "DonationTotal" NUMBER(10,1) NOT NULL,
    10
    SQL> PRIMARY KEY ("EventID")
    SP2-0734: UNKNOWN command beginning "Primary Ke..." - rest OF line ignored.
     
     
    Also if you do get the table created you are required to use full quotes around each column name else you receive the following possible errors:

    Code (SQL):
     
    SQL> SELECT TYPE ,DATE FROM donationevent;
    SELECT TYPE ,DATE FROM donationevent
    *
    ERROR at line 1:
    ORA-00936: missing expression
     
    SQL> SELECT eventid FROM donationevent;
    SELECT eventid FROM donationevent
    *
    ERROR at line 1:
    ORA-00904: "EVENTID": invalid identifier
     
    SQL>
     
     
    And you'll need to use the correct case for each column name or the latter error will again be displayed:

    Code (SQL):
     
    SQL> SELECT "eventID" FROM donationevent;
    SELECT "eventID" FROM donationevent
    *
    ERROR at line 1:
    ORA-00904: "eventID": invalid identifier
     
    SQL>
     
     
    So, yes, the statement creates a table, with case-sensitive column names requiring full quotes for recognition, column names using reserved words, a one-decimal number column where an integer is expected and an unnamed primary key constraint; not good practices to adopt.
     
  5. balu22777

    balu22777 Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    65
    Hi ! It is possiable to display any case and any type as your wish .but storing columns of table as Capital only in meta data area,so table name,table columns etc ..are meta data information those information stores as capital in metadata.
    if u want you can display as your wish
    Eg:if u create table like sample
    but it's stores as SAMPLE in metadata area.after creating that see that at
    select object_name from user_objects
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The metadata in the data dictionary may NOT be all capitals if the table name, column names or both are enclosed in full quotation marks and are typed in lower or mixed case:

    Code (SQL):
     
    SQL> CREATE TABLE "Test"(
      2  "MyTestId"  NUMBER,
      3  "MyTestName" varchar2(40),
      4  "MyTestDate" DATE
      5  );
    TABLE created.
    SQL>
    SQL> DESC "Test"
     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     MyTestId                                           NUMBER
     MyTestName                                         VARCHAR2(40)
     MyTestDate                                         DATE
    SQL> DESC test
    ERROR:
    ORA-04043: object test does NOT exist

    SQL> DESC Test
    ERROR:
    ORA-04043: object Test does NOT exist

    SQL> DESC TEST
    ERROR:
    ORA-04043: object TEST does NOT exist

    SQL>
    SQL> SELECT TABLE_NAME, column_name
      2  FROM user_tab_columns
      3  WHERE UPPER(TABLE_NAME) = 'TEST';
    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    Test                           MyTestDate
    Test                           MyTestName
    Test                           MyTestId
    SQL>
     
    Notice the data dictionary data IS in mixed case due to the surrounding "" in the create table statement.