12 Replies Latest reply: Apr 19, 2010 11:14 AM by 725039 RSS

    ORA-00917: missing comma

    767331
      i'm trying to insert some values in my table but i'm getting this error:
      ORA-00917: missing comma

      i'm using php and the query is like this:

      $insertar=oci_parse($conexion,'insert into medicos (fecha-presc,ciudad_presc,nombre) Values ("17-04-2010","mexico","Fulanito Mayor")');
      oci_execute($insertar);

      what coul be wrong??

      i created a sequence and a trigger for making the id_medico field auto-increment...


      thanks !!
        • 1. Re: ORA-00917: missing comma
          clcarter
          Probably not be picking up the double quotes as string delimiters, haven't done any php in a while but something more like ....

          ..."insert into .... values ( 'string1'[, 'stringN'] )"

          might work a bit better-
          • 2. Re: ORA-00917: missing comma
            767331
            i tried that, in fact i'm trying to insert data from the sql command line where i wrote this:

            insert into medicos (fecha-presc,ciudad_presc,nombre) Values ('17-04-2010','mexico','Fulanito Mayor');

            but i get these horrible error!!!

            any ideas???
            • 3. Re: ORA-00917: missing comma
              clcarter
              For date datatypes, its best to be specific ...

              ... values ( to_date('17-04-2010','DD-MM-YYYY'), ...

              Unless the database (or your session) has an NLS date format specified, counting on specific defaults is not an ideal situation. And the session overrides the database, if its set- before that particular insert statement, specify the format used for date values:

              alter session set nls_date_format = 'DD-MM-YYYY';
              • 4. Re: ORA-00917: missing comma
                696240
                Can you post a create table statement for the table you're trying to insert values into?
                • 5. Re: ORA-00917: missing comma
                  767331
                  i wrote this instead:

                  INSERT INTO medicos (fecha-presc,ciudad_presc,nombre) VALUES (to_date('17-04-2010','DD-MM-YYYY'),'mexico','Fulanito Mayor');

                  i'm still getting the missing comma error....

                  dont know what could be wrong =(
                  • 6. Re: ORA-00917: missing comma
                    Herald ten Dam
                    Hi,

                    is it the typo "fecha-presc"? You can't have a minus sign in a column name. Can it be that it is "fecha_presc"?

                    Herald ten Dam
                    Superconsult.nl
                    • 7. Re: ORA-00917: missing comma
                      767331
                      ok..

                      i did this

                      INSERT INTO medicos (fecha_presc,ciudad_presc,nombre) VALUES ('','Tokio','Perengano Lopez');

                      it inserts data with no porblem so it has to be with the date... but its my first time with oracle so... it would be nice some help ;)

                      thanks!!
                      • 8. Re: ORA-00917: missing comma
                        767331
                        Yes, it was a typo, i don't have the create statement cause i created the table in the object manager, would be better if i do it manually?
                        • 9. Re: ORA-00917: missing comma
                          725039
                          >
                          i don't have the create statement cause i created the table in the object manager, would be better if i do it manually?
                          >

                          No; it really does not matter if you do it manually using SQLPlus or if you use SQL Workshop.

                          Please post results of the following.

                          This will show us the structure of you table
                          DESC medicos
                          This will show us the DDL for your table.
                          SELECT dbms_metadata.get_ddl('TABLE','MEDICOS') 
                          FROM dual;
                          Regards,
                          Phiri
                          • 10. Re: ORA-00917: missing comma
                            767331
                            this is what i get when the
                            SELECT dbms_metadata.get_ddl('TABLE','MEDICOS') FROM dual;
                            statement is done
                            CREATE TABLE "HR"."MEDICOS" ( "ID_MEDICO" NUMBER NOT NULL ENABLE, "FECHA_PRESC" DATE, "CIUDAD_PRESC" VARCHAR2(4000), "NOMBRE" VARCHAR2(4000), CONSTRAINT "MEDICOS_PK" PRIMARY KEY ("ID_MEDICO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 
                            and this is what i get when i execute
                            DESC medicos
                            Tipo de Objeto      TABLE     Objeto      MEDICOS                                        
                            Table     Column     Tipo de Dato     Longitud     Precisión     Escala     Clave Primaria     Nulo     Valor por Defecto     Comentario
                            MEDICOS     ID_MEDICO     Number     -      -      -      1     -      -      -
                                  FECHA_PRESC     Date     7     -      -      -      nulo     -      -
                                  CIUDAD_PRESC     Varchar2     4000     -      -      -      nulo     -      -
                                  NOMBRE     Varchar2     4000     -      -      -      nulo     -      -
                                      1 - 4           
                            i hope this is helpfull cause i don´t know what else to do

                            thanks!!!
                            • 11. Re: ORA-00917: missing comma
                              Herald ten Dam
                              Hi,

                              I get the comma error if I execute:
                              INSERT INTO medicos (fecha-presc,ciudad_presc,nombre) VALUES (to_date('17-04-2010','DD-MM-YYYY'),'mexico','Fulanito Mayor');
                              I don't get the error and the insert succeeds:
                              INSERT INTO medicos (fecha_presc,ciudad_presc,nombre) VALUES (to_date('17-04-2010','DD-MM-YYYY'),'mexico','Fulanito Mayor');
                              I did mention earlier, it is the typo of fecha-presc. So please could you repeat the last statement.

                              Herald ten Dam
                              Superconsult.nl
                              • 12. Re: ORA-00917: missing comma
                                725039
                                >
                                ok..

                                i did this

                                INSERT INTO medicos (fecha_presc,ciudad_presc,nombre) VALUES ('','Tokio','Perengano Lopez');

                                it inserts data with no porblem so it has to be with the date... but its my first time with oracle so... it would be nice some help ;)

                                thanks!!
                                >


                                >
                                Hi,

                                is it the typo "fecha-presc"? You can't have a minus sign in a column name. Can it be that it is "fecha_presc"?

                                Herald ten Dam
                                Superconsult.nl
                                >

                                Herald was right then; you have a typo in your other SQL statements. Just issue the following code snippet and it should be fine.
                                INSERT INTO medicos (fecha_presc,ciudad_presc,nombre) VALUES (to_date('17-04-2010','DD-MM-YYYY'),'mexico','Fulanito Mayor');
                                Regards,
                                Phiri