12 Replies Latest reply on Dec 18, 2012 7:04 PM by Hoek

    ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!

    980595
      Hi, I am trying to insert data into a table which I created but I keep getting the error: missing comma. Could you please take a look at my coding and tell me where I have missed the comma.

      INSERT INTO EMPLOYEE_DATA

      (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)

      VALUES
      ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')
        • 1. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
          >
          Hi, I am trying to insert data into a table which I created but I keep getting the error: missing comma. Could you please take a look at my coding and tell me where I have missed the comma.

          INSERT INTO EMPLOYEE_DATA

          (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)

          VALUES
          ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')
          >
          What kind of column name is this?
          SALARY_(£),
          >
          You can't have embedded parenthesis like that.
          • 2. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
            sb92075
            977592 wrote:
            Hi, I am trying to insert data into a table which I created but I keep getting the error: missing comma. Could you please take a look at my coding and tell me where I have missed the comma.

            INSERT INTO EMPLOYEE_DATA

            (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)

            VALUES
            ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')
            post results from SQL below

            DESC EMPLOYEE_DATA
            • 3. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
              Hoek
              Welcome to the forum.
              Make sure to read {message:id=9360002} .
              What are the datatypes of the columns?
              Aren't some your 'strings' possibly of NUMBER or DATE datatype?
              Then always use explicit datatype conversion (TO_NUMBER and/or TO_DATE).
              Search and read about it here: http://www.oracle.com/pls/db112/homepage
              • 4. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                980595
                CREATE TABLE "EMPLOYEE_DATA"
                (     "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
                     "FIRST_NAME     " VARCHAR2(20) NOT NULL ENABLE,
                     "LAST_NAME     " VARCHAR2(20) NOT NULL ENABLE,
                     "DEPARTMENT" VARCHAR2(20) NOT NULL ENABLE,
                     "SALARY_(£)" NUMBER NOT NULL ENABLE,
                     "SALARY_PAIDON" DATE NOT NULL ENABLE,
                     CONSTRAINT "EMPLOYEE_DATA_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE,
                     CONSTRAINT "EMPLOYEE_RECORDS_UK1" UNIQUE ("DEPARTMENT") ENABLE
                )
                /
                ALTER TABLE "EMPLOYEE_DATA" ADD CONSTRAINT "EMPLOYEE_RECORDS_FK" FOREIGN KEY ("EMPLOYEE_ID")
                     REFERENCES "EMPLOYEE_SALES" ("EMPLOYEE_ID") ENABLE
                /
                • 5. Re: ORA-00917: missing comma.
                  6363
                  Welcome to the forum, please read the instructions

                  {thread:id=2174552}
                  {message:id=9360002}

                  Providing table descriptions, commands entered, database versions are all helpful. Upper case shouting and two dozen exclamations not so helpful.

                  What is the table description and column data types?
                  Do you really have a column called SALARY_(£)?
                  Is '3/11/2012' supposed to be a date? If so it will need to_date function applied with a format specifying whether it is March 11th or November 3rd
                  • 6. Re: ORA-00917: missing comma.
                    980595
                    Im a newbie to all this and I have not got a clue. I have created a table:

                    Column Name     Data Type     Nullable     Default     Primary Key
                    EMPLOYEE_ID     NUMBER(6,0)     No     -      1
                    FIRST_NAME      VARCHAR2(20)     No     -      -
                    LAST_NAME      VARCHAR2(20)     No     -      -
                    DEPARTMENT     VARCHAR2(20)     No     -      -
                    SALARY_(£)     NUMBER     No     -      -
                    SALARY_PAIDON     DATE     No     -      -



                    But I need to add data and I do not know how. I have looked everywhere. Please help. Why does it keep stating missing comma???
                    • 7. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                      Like I said you can't have embedded special characters. If you have them in your column name you need to quote the column name. Without the double quotes how does Oracle know when the column list ends?
                      drop table test
                      
                      create table test (empno number, "SALARY_(£)" number)
                       
                      insert into test (empno, SALARY_(£)) values (1, '20000')
                      
                      ORA-00917: missing comma
                      This works
                      insert into test (empno, "SALARY_(£)") values (1, '20000')
                      Everytime you use that column you will need to enclose it in double quotes. See the schema object naming conventions in the SQL Language doc.
                      http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements008.htm
                      >
                      4.You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.

                      5.Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

                      6.Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.
                      Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).
                      • 8. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                        980595
                        I have removed the £ sign. My code is like this now:

                        INSERT INTO EMPLOYEE_DATA

                        (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY, SALARY_PAIDON)

                        VALUES

                        ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')



                        However, another error has arisen. ORA-00904: "LAST_NAME": invalid identifier

                        HELP!
                        • 9. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                          >
                          I have removed the £ sign. My code is like this now:

                          INSERT INTO EMPLOYEE_DATA

                          (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY, SALARY_PAIDON)

                          VALUES

                          ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')

                          However, another error has arisen. ORA-00904: "LAST_NAME": invalid identifier
                          >
                          That error means the column with that name doesn't exist.

                          We can't help you if you don't show us what you do. Just saying 'I have removed ...' is useless. You can't remove a character from a column name.

                          You either create a new column or you recreate the table with a different column name.

                          So you need to start over and post the table DDL you now use.
                          • 10. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                            980595
                            I have sorted that out now - just renamed it. However, the missing comma is still there. This is the SQL Command which I have used:

                            INSERT INTO EMPLOYEE_DATA

                            (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)

                            VALUES

                            ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')



                            And this is the SQL of the actual table:

                            CREATE TABLE "EMPLOYEE_DATA"
                            (     "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
                                 "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
                                 "SURNAME" VARCHAR2(20) NOT NULL ENABLE,
                                 "DEPARTMENT" VARCHAR2(20) NOT NULL ENABLE,
                                 "SALARY" NUMBER NOT NULL ENABLE,
                                 "SALARY_PAIDON" DATE NOT NULL ENABLE,
                                 CONSTRAINT "EMPLOYEE_DATA_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE,
                                 CONSTRAINT "EMPLOYEE_RECORDS_UK1" UNIQUE ("DEPARTMENT") ENABLE
                            )
                            /
                            ALTER TABLE "EMPLOYEE_DATA" ADD CONSTRAINT "EMPLOYEE_RECORDS_FK" FOREIGN KEY ("EMPLOYEE_ID")
                                 REFERENCES "EMPLOYEE_SALES" ("EMPLOYEE_ID") ENABLE
                            /
                            • 11. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                              Go get a cup of coffee and sit down for a while. You seem to be confused and in a hurry.
                              >
                              I have sorted that out now - just renamed it. However, the missing comma is still there. This is the SQL Command which I have used:

                              INSERT INTO EMPLOYEE_DATA

                              (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)
                              >
                              How is that 'sorted out'? You didn't rename the column in the column list above and you didn't put double quotes around it.

                              And your column list now says SURNAME but you got an exception that said LAST_NAME does not exist.
                              • 12. Re: ORA-00917: missing comma. HELP!!!!!!!!!!!!!!!!!!!!!!!!
                                Hoek
                                977592 wrote:
                                I have sorted that out now - just renamed it. However, the missing comma is still there. This is the SQL Command which I have used:

                                INSERT INTO EMPLOYEE_DATA

                                (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)

                                VALUES

                                ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')



                                And this is the SQL of the actual table:

                                CREATE TABLE "EMPLOYEE_DATA"
                                (     "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
                                     "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
                                     "SURNAME" VARCHAR2(20) NOT NULL ENABLE,
                                     "DEPARTMENT" VARCHAR2(20) NOT NULL ENABLE,
                                     "SALARY" NUMBER NOT NULL ENABLE,
                                     "SALARY_PAIDON" DATE NOT NULL ENABLE,
                                     CONSTRAINT "EMPLOYEE_DATA_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE,
                                     CONSTRAINT "EMPLOYEE_RECORDS_UK1" UNIQUE ("DEPARTMENT") ENABLE
                                )
                                /
                                ALTER TABLE "EMPLOYEE_DATA" ADD CONSTRAINT "EMPLOYEE_RECORDS_FK" FOREIGN KEY ("EMPLOYEE_ID")
                                     REFERENCES "EMPLOYEE_SALES" ("EMPLOYEE_ID") ENABLE
                                /
                                Running änd fixing your exact example:
                                SQL> CREATE TABLE "EMPLOYEE_DATA"
                                  2  ( "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
                                  3  "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
                                  4  "SURNAME" VARCHAR2(20) NOT NULL ENABLE,
                                  5  "DEPARTMENT" VARCHAR2(20) NOT NULL ENABLE,
                                  6  "SALARY" NUMBER NOT NULL ENABLE,
                                  7  "SALARY_PAIDON" DATE NOT NULL ENABLE,
                                  8  CONSTRAINT "EMPLOYEE_DATA_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE,
                                  9  CONSTRAINT "EMPLOYEE_RECORDS_UK1" UNIQUE ("DEPARTMENT") ENABLE
                                 10  )
                                 11  /
                                
                                Table created.
                                
                                SQL> INSERT INTO EMPLOYEE_DATA
                                  2  (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)
                                  3  VALUES
                                  4  ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012');
                                (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)
                                                                                     *
                                ERROR at line 2:
                                ORA-00917: missing comma
                                
                                
                                SQL> INSERT INTO EMPLOYEE_DATA
                                  2  (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY, SALARY_PAIDON)
                                  3  VALUES
                                  4  ('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012');
                                
                                1 row created.
                                
                                SQL> select * from employee_data;
                                
                                EMPLOYEE_ID FIRSTNAME            SURNAME              DEPARTMENT               SALARY SALARY_PAIDON
                                ----------- -------------------- -------------------- -------------------- ---------- --------------
                                          1 David                Smith                IT                           20 03-11-2012 00:00:00
                                
                                SQL> rollback;
                                
                                Rollback complete.
                                
                                SQL> 
                                SQL> INSERT INTO EMPLOYEE_DATA
                                  2  (EMPLOYEE_ID, FIRSTNAME, SURNAME, DEPARTMENT, SALARY, SALARY_PAIDON)
                                  3  VALUES
                                  4  (to_number('1'), 'David', 'Smith', 'IT', to_number('20,000', '99G999', 'nls_numeric_characters=
                                .,'), to_date('3/11/2012', 'dd/mm/yyyy'));
                                
                                1 row created.
                                
                                SQL> select * from employee_data;
                                
                                EMPLOYEE_ID FIRSTNAME            SURNAME              DEPARTMENT               SALARY SALARY_PAIDON
                                ----------- -------------------- -------------------- -------------------- ---------- --------------
                                          1 David                Smith                IT                        20000 03-11-2012 00:00:00
                                
                                SQL> 
                                Now, again: use TO_NUMBER and TO_DATE.
                                See the difference for SALARY, do you have 20 or 20000?