SQL DML error while creating table in Oracle – ORA-00907: missing right parenthesis

I was creating a table the other day in an Oracle database when I got a strange error as shown in the below snapshot.

The error said that ORA-00907: missing right parenthesis.

So the first thing that I did was check the parenthesis and they all were found to be in order. Strange right. If an error says that right parenthesis are missing, then you would expect to find that you have an unequal number of brackets while issuing the create table. But the error is a bit more subtle.

 
Check the entry for FMONTH. The specification of the column is VARCCHAR instead of VARCHAR. This is what was throwing an error. Not a missing parenthesis! I corrected the error as shown below and the query ran perfectly well.    

Now, the thing to remember is that SQL is a higher level language. This means that you just tell what the system has to do at an abstract level (most of us will relate to this as SELECT…FROM…WHERE. We don’t need to worry about the internal implementation details. This is handled by the database platform for us.) This means that the SQL interpreter will basically try to make sense of any statement that you give so long as it conforms to a given syntax. Thus VARCCHAR error was not flagged and it gave a generic error saying that right parenthesis is missing. Only way to fix these errors is one step at a time. 

(On the side, observe the length of the ACTUALS column. In the first snapshot, it is 40 whereas in the second on it is 38. This is because Oracle tags NUMBER field length to be between 1 and 38. I fixed that error as well and that error is pretty much straightforward since it is easy to design a parser for such restricted use cases)