Top Common Errors in SQL and Oracle

Whether you’re speeding through your work, 60 hours deep into your work week, or just have brain fog – accidents happen. It can be easy to make a mistake when it comes to working in a database. One minute you’re double fisting coffee and taking on your work like a champ, the next you’re fighting error messages. Here are the most common errors in SQL and Oracle, and how to fix them.  

General 

1. Syntax errors are the most common error response 
2. It’s easy to make a spelling error or miss a comma, especially if you’re moving quickly 
3. You might have missed a clause (missed from, join, select) 
4. There might be issues with case sensitivity 
5. Double check brackets, quotes, and parentheses 
6. You’ll see the message - “SQL ERROR: syntax error at or near” or Syntax error in SQL statement” 

Solution  

• Comment out blocks of SQL to hone in on the location of the problem 
• Check for misspelled words, incorrect punctuation (commas) 
• Google all the functions used in the query and verify that they exist and are being used correctly 
• Verify all objects exist 
• Use a SQL editor that has syntax highlighting 
• Leverage tools that also validate database objects being referenced in the SQL and can auto fill column names, etc 

• SSMS (SQL Server Management Studio) (for SQL Server) and similar tools for Oracle like SQL Developer and similar tools or other database engines provide this capability and can save a lot of time when coding complicated SQL joins or procedural code. 

1. Referencing an order or query that doesn’t exist 
2. Wrong query language or using language from a different database 
3. Using old keywords 

Solution 

• Use try and catch statements 
• Make sure you’re using updated, SQL-only language  

Most common ORA error codes: 

• ORA-00904: “specified number of rows exceeds maximum”  

• • Check that your column name exists in the table you’re referring to 
  • Check that you’re referring to the correct alias when referring to the column 
  • Check that you’re using the correct table alias for the column  
  • Check that you’re not referring to a column alias inside a WHERE clause 

• • Check that the column name is 30 characters or less 
  • Check that the column name contains only alphanumeric characters 
  • Check that it is not a reserved word 

• ORA-00900: “invalid SQL statement” 

• • Make sure Procedural Option is installed 

• ORA-00902: “invalid datatype” 

• • Check for syntax errors – especially underscores  

• ORA-12154 - The service name could not be found  

• • Check that the TNSNAMES.ORA file exists 
  • Check that TNSNAMES.ORA has no syntax errors 

• • Check that TNSNAMES.ORA has your service name in it 
  • Check that TNSNAMES.ORA has read permission 
  • Run the TNSPING Utility specifying the service to validate the lookup and that the service is reachable while also seeing all the key attributes of the connection along with the location of the TNSNAMES.ORA file being used in the current environment 
  • Determine if the TNS_ADMIN environment variable is set in your session and that the location it points to contains the correct TNSNAMES.ORA file with a entry for the service you're trying to reach. 

• ORA-12162 - The connection description has an error 

• • Check if your $ORACLE_HOME and $ORACLE_SID are correct 

• • Check if your TNS:net service name is incorrectly specified 

• ORA-12163 - The connection description is too long 

• • Check the net service name's connect descriptor in the local naming file or in the directory server  
  • Use a smaller connect descriptor 

• ORA-12197 - The connection description has an error 

• • Check the syntax of the connect descriptor 
  • Leverage Oracle's technical support knowledgebase at support.oracle.com which is available to licensed users of the products who have a valid support contract 

• • There are many good articles and "troubleshooters" to help with common issues like TNSNAMES resolution issues, etc 

• ORA-12198 - A path could not be found via interchanges 

• • Assure that Interchanges necessary to get to the desired destination are up and have available capacity for an additional connection 
  • Check that the correct community and protocol have been specified in the CMANAGER address used 

• ORA-12203 - Unable to connect to destination 

• • Verify that the net service name you entered was correct 
  • Verify that the ADDRESS portion of the connect descriptor which corresponds to the net service name is correct 
  • Ensure that the destination process (for example the listener) is running at the remote node 

• ORA-12208 - tnsnav.ora could not be found 

• • Ensure that the ORACLE environment is set up appropriately on your platform and that a TNSNAV.ORA file is present 

• ORA-12210 - Improper configuration of tnsnav.ora 

• • Check the syntax of the TNSNAV.ORA file 

• ORA-12500 - Could not start a dedicated server process 

• • Turn on tracing at the ADMIN level and reexecute the operation 
  • Verify that the ORACLE Server executable is present and has execute permissions enabled 

• • Ensure that the ORACLE environment is specified correctly in LISTENER.ORA 
  • Check that the Oracle Protocol Adapter that is being called is installed on the local hard drive 
  • Check that the correct Protocol Adapter are successfully linked 

• ORA-12504 - SID could not be resolved 

• • Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service name 

• • Reload the listener parameter file using reload [<listener_name>] 
  • If using a service name, check that the connect descriptor corresponding to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component in the CONNECT_DATA 

• ORA-12514 - TNS:listener does not currently know of service requested in connect 

• • Check that database is available 
  • Check that the service names are matched 

What Solvaria does 

If you’re constantly running into errors, your data problems might lay beyond basic typos and syntax missteps. Getting a database health assessment can be a great way to target all of your database’s issues in one fell swoop.