CODEWITHSUNDEEP
sqloracle-databasesyntax-errorsql-insert
abderrahim
abderrahim

Reputation: 33

Missing comma in SQL while insert

Code:

CREATE TABLE PERSONNE1 ( 
   NUMP NUMBER, 
   PRENOM VARCHAR2(25), 
   NOM VARCHAR2(25), 
   DATENAIS DATE, 
   CONSTRAINT PK_NUMP PRIMARY KEY (NUMP)
);

create sequence numpersonne
            start with 0
            increment by 2
            minvalue -1;

SQL> insert into PERSONNE1 (numpersonne.nextval, "Jack", "Nicholson", to_date('04/22/1937','dd/mm/yyyy'));
    insert into PERSONNE1 (numpersonne.nextval, "Jack", "Nicholson", to_date('04/22/1937','dd/mm/yyyy'))

Error:

ERROR at line 1:
ORA-00917: missing comma

Upvotes: 0

Views: 2174

Answers (2)

Mureinik
Mureinik

Reputation: 311228

String literals in SQL are denoted by single quotes ('), not double quotes ("). More importantly, as noted in the comments, you're missing the values keyword:

insert into PERSONNE1
values -- was missing
(numpersonne.nextval, 
 'Jack', -- Fix the quotes here
 'Nicholson', -- And here
 to_date('04/22/1937','mm/dd/yyyy'));

Edit:
The date format was also wrong - you need to switch dd and mm.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191275

The immediate cause of the error you're seeing is that you've got your values in the wrong place; at the moment they're (sort of) part of the column list, and you can't have a function call inside that - so the opening parenthesis is being flagged. And that's because you're missing the values keyword.

As @Mureinik said, the string enclosure is a single quote, not a double quote; and you also have your day and month the wrong way round:

insert into PERSONNE1
values (numpersonne.nextval, 'Jack', 'Nicholson', to_date('04/22/1937','mm/dd/yyyy'));

or using a date literal to avoid that potential for confusion:

insert into PERSONNE1
values (numpersonne.nextval, 'Jack', 'Nicholson', date '1937-04-22');

It is also a good idea to explicitly list the columns you're inserting into:

insert into PERSONNE1 (NUMP, PRENOM, NOM, DATENAIS)
values (numpersonne.nextval, 'Jack', 'Nicholson', date '1937-04-22');

Upvotes: 2

Related Questions