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!

SQL Error: ORA-00904: : invalid identifier

Discussion in 'SQL PL/SQL' started by AngryPenguin, Apr 16, 2013.

  1. AngryPenguin

    AngryPenguin Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I'm creating a rudimentary database to get the hang of Oracle syntax, but am having issues flushing out an error I am receiving (SQL Error: ORA-00904). Attached is some source with description:


    Code (SQL):
    CREATE TABLE tblEmployee
    (
        EmployeeID          INTEGER         NOT NULL,
        EmpFName            VARCHAR2 (25)   NOT NULL,
        EmpLName            VARCHAR2 (25)   NOT NULL,
        EmpSSN              VARCHAR2 (12)   NOT NULL,
        JobTitle            VARCHAR2 (25),
        ADD CONSTRAINT "tblEmployee_pk"
           PRIMARY KEY (EmployeeID)
    );

    CREATE TABLE tblStation
    (
        StationID           INTEGER         NOT NULL,
        StationDescr        VARCHAR2(50),
        ADD CONSTRAINT "tblStation_pk"
           PRIMARY KEY (StationID)
    );

    CREATE TABLE tblShift
    (
        ShiftID             INTEGER         NOT NULL,
        DayofWeek           CHARACTER(3),
        StartTime           DATE,
        EndTime             DATE,
        ADD CONSTRAINT "tblShift_pk"
            PRIMARY KEY (ShiftID)
    );

    CREATE TABLE tblEmployeeSchedule
    (
        AssignmentID            INTEGER     NOT NULL,
        EmployeeID              INTEGER     NOT NULL,
        StationID               INTEGER     NOT NULL,
        ShiftID                 INTEGER     NOT NULL,
        ADD CONSTRAINT "tblEmployeeSchedule_pk"
           PRIMARY KEY (AssignmentID),
        ADD CONSTRAINT "tblEmployeeScheduleEmployee_fk"
            FOREIGN KEY (EmployeeID),
        ADD CONSTRAINT "tblEmployeeScheduleStation_fk"
            FOREIGN KEY (StationID),
        ADD CONSTRAINT "tblEmployeeScheduleShift_fk"
            FOREIGN KEY (ShiftID)
    );

    COMMIT;
    Within every table creation function, I am getting the error:
    Error report:
    SQL Error: ORA-00904: : invalid identifier
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:
    committed.


    This is frustrating because I am not clear on what part of my syntax is incorrect. Is there something that needs to be Oracle specific in there? Searching the error reveals several possible causes which leads me here. Also, there is a nondescript error starting at my CREATE TABLE tblEmployee function.
     
  2. AngryPenguin

    AngryPenguin Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I have debugged most of my statements now. There were multiple syntax errors in the first post that would have been easy for an experienced eye to pick up on. One thing I have a question about is why the initial DROP TABLE block does not seem to work? I have to run drop table statements in a separate script to work with this table creation script, else I receive object already exist errors. How can I adjust this code to where it for sure will DROP TABLES of the names listed before creation?

    Code (SQL):
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE tblEmployee';
      EXECUTE IMMEDIATE 'DROP TABLE tblStation';
      EXECUTE IMMEDIATE 'DROP TABLE tblEmployeeSchedule';
      EXECUTE IMMEDIATE 'DROP TABLE tblShift';
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('');
    END;
    /

    -- Begin db create tables

    CREATE TABLE tblEmployee
    (
        EmployeeID          INTEGER         NOT NULL,
        EmpFName            VARCHAR2 (25)   NOT NULL,
        EmpLName            VARCHAR2 (25)   NOT NULL,
        EmpSSN              VARCHAR2 (12)   NOT NULL,
        JobTitle            VARCHAR2 (25),
        CONSTRAINT tblEmployee_pk PRIMARY KEY (EmployeeID) 
    );

    CREATE TABLE tblStation
    (
        StationID           INTEGER         NOT NULL,
        StationDescr        VARCHAR2(50),
        CONSTRAINT tblStation_pk PRIMARY KEY (StationID)
    );

    CREATE TABLE tblShift
    (
        ShiftID             INTEGER         NOT NULL,
        DayofWeek           CHARACTER(3),
        StartTime           DATE,
        EndTime             DATE,
        CONSTRAINT tblShift_pk PRIMARY KEY (ShiftID)
    );

    CREATE TABLE tblEmployeeSchedule
    (
        AssignmentID            INTEGER     NOT NULL,
        EmployeeID              INTEGER     NOT NULL,
        StationID               INTEGER     NOT NULL,
        ShiftID                 INTEGER     NOT NULL,
        CONSTRAINT tblEmployeeSchedule_pk PRIMARY KEY (AssignmentID),
        CONSTRAINT tblEmployeeScheduleEmp_fk FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
        CONSTRAINT tblEmployeeScheduleStation_fk FOREIGN KEY (StationID) REFERENCES tblStation(StationID),
        CONSTRAINT tblEmployeeScheduleShift_fk FOREIGN KEY (ShiftID) REFERENCES tblShift(ShiftID)
    );

    COMMIT;
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi [Penguin-don't-be-so-angry],

    First and foremost, dude u're merging multiple non-synchronous issues in a single thread. The standard way is to post separate threads for separate issues.

    Anyways, Cheers for u've resolved the earlier issue urself :hurray (its safer also, u got it fixed b4 some1'd scowl on u "Hey! Go and learn the syntax first" .. :D )

    Coming to the second issue, Please insert a commit statement after the Begin..End block and then see what's happening.
     
  4. AngryPenguin

    AngryPenguin Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Currently the script seems to be working fine; well, there are no errors. And there is a lot of additional code I have added. It seems that particular block I was talking about is suited for when you are initially creating your database connection and the initial tables. The script as a whole is more or less a database setup script, which is what im working on. But while im working on it, I do not require that after the initial database has been setup.

    Indeed, I usually try to resolve the issue myself and I should post in separate threads for separate issues hereafter. I do have some questions about some phenomenon that I am seeing with the additional scripting I have added to my setup script, but I will post that in a new thread :)