ORA-00917: missing comma

SQL Error: ORA-00917: missing comma error occurs when a comma is missing in the VALUES clause of the Oracle insert statement. In the insert statement, a comma is used to separate a list of values enclosed by parenthesis. If the comma was missing, the insert statement could not identify all of the values in the VALUES clause. The insert statement will fail to store the values into the Oracle table. In this case, the error message SQL Error: ORA-00917: missing comma will be displayed.

The VALUES clause of the insert statement contains a list of column values to be stored in the table. The comma will be used to separate each value. The list of all values is enclosed by open and close parenthesis. The error message will be displayed if the comma is missing in the list of values where it is required. Oracle will validate the comma operator until it finds the close parentheses.If neither a comma operator nor a closing parenthesis is found in the VALUES clause of the insert statement, Oracle will throw an error SQL Error: ORA-00917: missing comma.



When the ORA-00917 error occur

If the comma operator and the close parenthesis are missing from the insert statement’s VALUES clause, the insert statement will fail to save the values in the database tables. In the insert statement, the comma operator is absent where it should be. If you create an insert statement without a comma and closing parenthesis in the VALUES clause, the error will occur.

Problem

insert into emp (id,name) values (1,2;

Error

Error starting at line : 36 in command -
insert into emp (id,name) values (1,2
Error at Command Line : 36 Column : 37
Error report -
SQL Error: ORA-00917: missing comma
00917. 00000 -  "missing comma"


Root Cause

The VALUES clause of the insert statement will be used to identify and save the list of values. The comma operator is used to separate each value. All of the values are surrounded by open and closed parenthesis. If neither the comma operator nor the close parenthesis are present in the list, the insert statement is unable to identify a list of all the values. The error message will be displayed in this scenario.



Solution 1

If neither the comma operator nor the close parenthesis are present in the list, the insert statement is unable to identify a list of all the values. Add the closing parenthesis if the list of values is complete. If the values are missing, add them by separating them using the comma operator. If the comma operator is missing between the values, insert it where it is required.

Problem

insert into emp (id,name) values (1,2;

SQL Error: ORA-00917: missing comma
00917. 00000 -  "missing comma"

Solution

insert into emp (id,name) values (1,2);


Solution 2

Single quotes should be used to surround the string and date values. If the single quotes in the insert statement are missing, the error message will be shown. The insert statement was unable to recognize the string and date value because the single quotation was missing. The insert statement failed to identify the closing parenthesis, hence the list of values could not be recognized.

Problem

insert into servers (id,name,ip) values (1,'production',192.168.0.1);

SQL Error: ORA-00917: missing comma
00917. 00000 -  "missing comma"

Solution

insert into servers (id,name,ip) values (1,'production','192.168.0.1');


Solution 3

If the database table contains unicode characters, the insert statements must appropriately handle the unicode characters. If there is a problem with the unicode characters, the insert statement should not be completed. Run the query below and confirm that the value is ‘AL32UTF8’. If this is not the case, the insert statement should be used in accordance with the unicode support.

select * from sys.props$ where name='NLS_CHARACTERSET';

Problem

insert into emp (id,name) values (1,'????');

Error

SQL Error: ORA-00917: missing comma
00917. 00000 -  "missing comma"


Solution 4

If you use the sequence value in the insert statement, be sure you use the correct syntax. Check if the sequence exists in the Oracle database. If a problem occurs while using database objects such as sequence, the “SQL Error:ORA-00917: missing comma” error may occur.



Leave a Reply