How to Fix ORA-00917: Missing Comma in Oracle

ORA-00917 means Oracle expected a comma between two items of a list, in a column list or a VALUES list, and found the next item instead. It is almost always a typing slip in an INSERT statement. The column position in the error points to the item that should have been preceded by a comma.

Reproduce the Error

Example:

create table demo_cities (city_id number, city_name varchar2(40), country varchar2(2));
insert into demo_cities (city_id, city_name, country) values (1 'Delhi', 'IN');
insert into demo_cities (city_id city_name, country) values (1, 'Delhi', 'IN');

Output:

Table DEMO_CITIES created.

Error starting at line : 2
In command -
insert into demo_cities (city_id, city_name, country) values (1 'Delhi', 'IN')
Error at Command Line : 2 Column : 65
Error report -
SQL Error: ORA-00917: missing comma

Error starting at line : 3
In command -
insert into demo_cities (city_id city_name, country) values (1, 'Delhi', 'IN')
Error at Command Line : 3 Column : 34
Error report -
SQL Error: ORA-00917: missing comma

The first INSERT misses the comma after 1 in the VALUES list, the second after city_id in the column list. The reported columns, 65 and 34, are where 'Delhi' and city_name start.

Fix: Add the Comma

Example:

insert into demo_cities (city_id, city_name, country) values (1, 'Delhi', 'IN');
select * from demo_cities;

Output:

1 row inserted.

   CITY_ID CITY_NAME    COUNTRY
__________ ____________ __________
         1 Delhi        IN

How to Avoid It

• Write one column and one value per line in long INSERT statements, with the commas at the start or end of each line, so a missing one is visible.
• Count: the column list and the VALUES list must have the same number of items.
• Generate INSERT statements with a tool, such as SET SQLFORMAT INSERT in SQLcl, rather than typing them.

Related Guides

• How to Fix ORA-00907: Missing Right Parenthesis in Oracle
• How to Format Query Output in SQLcl (SET SQLFORMAT)

Conclusion

ORA-00917 marks a missing comma in a list, usually in an INSERT. Go to the reported column, add the comma before that item, and lay out long lists one item per line.