6 Troubleshooting Oracle Errors

6.1 Module Introduction

In this module, students will learn how to interpret and troubleshoot common Oracle SQL errors. Students will explore how Oracle reports errors, distinguish between syntax and execution problems, and review common error messages and their causes. This foundational knowledge is essential for diagnosing issues when querying data in a reporting context.

Reference: Oracle SQL by Example (4th Edition), Chapter 3 (Lab 3.2), Appendix G

6.2 Explanation

6.2.1 How Oracle Reports Errors

Oracle SQL error messages typically include:

The line and column where the error was detected
The Oracle error code (e.g., ORA-00904)
A brief message describing the issue

When Oracle finds a problem, the line and column it reports show where it detected the error — the point where your statement stopped making sense to it. The real mistake is often slightly earlier. For example, a missing comma can make Oracle flag the next keyword instead of the comma itself. So it helps to:

Focus on fixing the first error reported
If the reported spot looks correct, check just before it
Re-run the query after each fix to reveal additional issues

Reference: Lab 3.2

6.2.2 Common Oracle SQL Errors

Error Code	Description	Common Causes
ORA-00904	Invalid identifier	Misspelled column or alias name
ORA-00923	FROM keyword not found where expected	Missing comma or keyword in SELECT
ORA-00933	SQL command not properly ended	Improper syntax formatting
ORA-00936	Missing expression	Incomplete SELECT clause
ORA-00942	Table or view does not exist	Incorrect table name or no access
ORA-01017	Invalid username/password	Incorrect login credentials
ORA-12154	TNS could not resolve connect identifier	Misconfigured or missing TNS entry

Reference: Chapter 3, Lab 3.2; Appendix G

6.2.3 Syntax vs. Execution Errors

Syntax Errors: The SQL statement is not a valid command. Example: ORA-00933 (SQL command not properly ended)
- Notice in the example below that SQL Developer has underlined the FROM keyword in red. This often means that something is wrong with the way we are specifying our query.

Execution Errors: The SQL query is a valid command, but cannot be executed. Example: ORA-00942 (table or view does not exist)

Understanding the type of error can help determine whether to revise SQL structure or verify the available schema objects.

Reference: Lab 3.2

6.2.4 Using Oracle Documentation and Tools

Oracle provides tools and references for error resolution:

Search Oracle documentation for the error code
Use Oracle SQL Developer’s hover and highlighting features to detect syntax issues

Appendix G explains common error prefixes:

ORA- : Oracle database errors
TNS- : Network-related errors
SP2- : SQL*Plus errors

Reference: Appendix G

6.2.5 Debugging Strategies

Start Simple

Begin with basic SELECT statements before adding complexity
If something breaks, remove pieces until it works, then add back one at a time
Save working queries before making changes

Read the Error Messages

Don’t ignore the red error text - it tells you exactly what’s wrong
Look for keywords like “invalid column” or “table not found”
The error often points to the specific line with the problem

Check the Basics

Verify table and column names are spelled correctly
Make sure you’re connected to the right database
Confirm you have permission to access the tables you’re querying

Common Mistakes

Missing commas between column names in SELECT lists, or having an extra comma at the end
Using single quotes for column names instead of double quotes
Mixing up = (equals) and == (not used in SQL)

Still Stuck?

Copy/paste the exact error message into a search engine
Paste the query and its error message or unexpected results into an AI assistant and ask it to explain the problem
Ask yourself: “What am I trying to accomplish?” and break it into smaller steps
Use SQL Developer’s autocomplete to help with syntax
Don’t be afraid to start over with a simpler approach

6.3 Demo

Not every problem announces itself with an ORA- error. Some queries run successfully but return the wrong results — and those are the easiest to miss, because there is no red error text to search for and no line number to inspect.

The query below is meant to list every course that is not listed as a prerequisite for another course:

SELECT course_no, description
FROM course
WHERE course_no NOT IN (SELECT prerequisite FROM course);

It runs without any error, but it returns zero rows — even though 14 courses qualify. This is a good case for an AI assistant: paste the query into the Oracle SQL Optimizer and ask why it returns no rows. See the Demo answer at the end of this module for the fix and an explanation.

6.4 Exercises

Use the STUDENT schema for the following exercises. Each of these queries is intentionally incorrect and will generate a specific Oracle error. Read the error message and revise the query to correct it.

Identify and fix the error in this query:

SELECT firstnme, last_name
FROM student;

Identify and fix the error in this query:

SELECT student_id first_name last_name
FROM student;

Identify and fix the error in this query:

SELECT *
FROM studnt;

6.5 Q&A

Food for thought:

Which of the previous exercises were syntax errors?
Which of the previous exercises were execution errors?

Open discussion of:

Common error messages encountered by students
Best practices for debugging SQL in Oracle Developer
When to look up error codes in Oracle documentation
How to interpret error line numbers and positioning

6.6 Additional Resources

Oracle SQL by Example, Lab 3.2 and Appendix G
Oracle Error Messages Manual
SQL Developer error highlighting and documentation lookup features
Oracle SQL Optimizer

6.7 Answers

Corrected query for misspelled column name:

SELECT first_name, last_name
FROM student;

Corrected query for missing comma:

SELECT student_id, first_name, last_name
FROM student;

Corrected query for nonexistent table name:

SELECT *
FROM student;

Demo answer:

The original query returns no rows because the PREREQUISITE column contains NULL values. When the subquery in a NOT IN clause returns even one NULL, Oracle cannot confirm that a course number is “not in” the list, so every row evaluates to unknown and nothing is returned. Exclude the NULLs (or rewrite with NOT EXISTS):

SELECT course_no, description
FROM course
WHERE course_no NOT IN (SELECT prerequisite FROM course WHERE prerequisite IS NOT NULL);