SQL Error: ORA-00917: missing comma when inserting values into Customer table:

Question

Insert into Les_Mills_Customers
( 
    CUSTOMER_ID         
    ,C_USERNAME       
    ,C_TITLE                  
    ,F_NAME                  
    ,L_NAME                  
    ,C_MESSAGE               
    ,C_ADDRESS               
    ,C_GENDER                 
    ,C_MOBILE                  
    ,C_NOTES                 
    ,C_PAYMENT_MODE           
    ,C_EMAIL                 
    ,C_TYPE                   
    ,C_PICTURE                       
    ,C_JOINDATE                     
    ,C_TIMETABLES
 ) 
 values 
 ( 
    50
    ,’A_Joe’
    ,’Mrs’,
    ’Allison’
    ,’Joe’
    ,’RPM’
    ,’Claudelands’
    ,’F’
    ,0273252302
    , ’RPM’
    ,’E’
    ,’123@gmail.com’
    ,'NULL'
    ,’NULL’
    ,To_DATE ('20-02-15','DD-MM-YY'),01
 )

Error at Command Line : 328 Column : 171 Error report - SQL Error: ORA-00917: missing comma 00917. 00000 - "missing comma" *Cause:
*Action:

@JeffreyKemp It's not the comma, rather it's the issue with single-quotation marks. — Lalit Kumar B, Oct 13 '15 at 05:40
You may be right, but there was insufficient info to tell whether that was a copy-and-paste error (yes, I've seen developers use MS Word to copy-and-paste code!). I've never seen any other scenario where a developer ends up with those stupid "smart quote" characters in their code. — Jeffrey Kemp, Oct 14 '15 at 00:46

score 3 · Answer 1 · answered Oct 13 '15 at 05:44

There are multiple issues with your INSERT statement:

’A_Joe’,’Mrs’,’Allison’,’Joe’,’RPM’,’Claudelands’,’F’,0273252302, 
’RPM’,’E’,’123@gmail.com’,'NULL',’NULL’,To_DATE ('20-02-15','DD-MM-YY')

You must enclose the strings within single-quotation marks. ’ is not single quote, ' is a single quote. Just like you used in the TO_DATE function.
Better use YYYY format, else you will reinvent the Y2K bug.
NULL should not be used within single quotes, just leave the keyword as it is. Else, you will store it as a string, and not the NULL value.

score 1 · Accepted Answer · answered Oct 13 '15 at 06:19

As noted by @Lalit, you must enclose strings with single quotes. Double quotes can be used in some databases products, with properly settings, but this configuration is not ANSI compatible and must be avoided.

Please do that only in raw SQL statements hand made. Pass strings to SQL commands in executed code will let you vulnerable to SQL injection attacks. Using SQL parameters is the right way.

And beware names like Sant'Anna, with apostrophes in them. Apostrophes are represented as single-quotes very often. In that case, double the apostrophes to represent a single apostrophe.

INSERT INTO TABLE1 (NAME) VALUE ('Sant''Anna')

"*And beware names like Sant'Anna*" No need of all that effort from 10g on wards. See [Quoting string literal technique to avoid errors due to single-quotation marks in the string](http://lalitkumarb.com/2014/12/31/quoting-string-literal-technique-to-avoid-erros-due-to-single-quotation-marks-in-the-string/) And my answer here http://stackoverflow.com/a/27373394/3989608 — Lalit Kumar B, Oct 13 '15 at 07:14

score 0 · Answer 3 · answered Oct 13 '15 at 06:20

The INSERT INTO syntax is below, kindly modified your code, you have an issue the way you insert the values, you are not using single quotes (').

Insert into Les_Mills_Customers
( 
    CUSTOMER_ID         
    ,C_USERNAME       
    ,C_TITLE                  
    ,F_NAME                  
    ,L_NAME                  
    ,C_MESSAGE               
    ,C_ADDRESS               
    ,C_GENDER                 
    ,C_MOBILE                  
    ,C_NOTES                 
    ,C_PAYMENT_MODE           
    ,C_EMAIL                 
    ,C_TYPE                   
    ,C_PICTURE                       
    ,C_JOINDATE                     
    ,C_TIMETABLES
 ) 
 values 
 ( 
    50
    , 'A_Joe'
    , 'Mrs'
    , 'Allison'
    , 'Joe'
    , 'RPM'
    , 'Claudelands'
    , 'F'
    , 0273252302
    , 'RPM'
    , 'E'
    , '123@gmail.com'
    , NULL
    , NULL
    , To_DATE ('20-02-15','DD-MM-YY')
    , 01
 )

@LalitKumarB , yup .. but that is the code above, so i didn't modified it , because it is not mine :) i don't know if the person who ask wanted it, its up to him. — japzdivino, Oct 13 '15 at 07:40

SQL Error: ORA-00917: missing comma when inserting values into Customer table:

3 Answers3