SQL Developer throws missing SELECT error when using line comments with double dashes

Data and Databases
1

I’m encountering a strange problem with Oracle SQL Developer where including a line comment disrupts my query from executing correctly.

The following query works without any issues when run:

select 12 id, date '2024-01-15' orderDate, 25 qty, 148.75 amount, 2 status from dual union all
select 12 id, date '2024-01-16' orderDate, 33 qty, 267.89 amount, 2 status from dual union all
select 12 id, date '2024-01-17' orderDate, 51 qty, 423.56 amount, 2 status from dual union all
select 12 id, date '2024-01-18' orderDate, 19 qty, 315.42 amount, 2 status from dual union all
select 12 id, date '2024-01-20' orderDate, 87 qty, 654.33 amount, 2 status from dual union all
select 12 id, date '2024-01-21' orderDate, 76 qty, 589.67 amount, 2 status from dual union all
select 12 id, date '2024-01-22' orderDate, 145 qty, 789.12 amount, 2 status from dual union all
select 12 id, date '2024-01-25' orderDate, 38 qty, 456.78 amount, 2 status from dual union all
select 12 id, date '2024-01-28' orderDate, 42 qty, 678.90 amount, 2 status from dual union all
select 12 id, date '2024-01-30' orderDate, 156 qty, 234.56 amount, 2 status from dual union all
select 12 id, date '2024-02-01' orderDate, 67 qty, 567.89 amount, 2 status from dual union all
select 12 id, date '2024-02-03' orderDate, 23 qty, 345.67 amount, 2 status from dual union all
select 12 id, date '2024-02-05' orderDate, 89 qty, 123.45 amount, 2 status from dual union all
select 12 id, date '2024-02-07' orderDate, 134 qty, 890.12 amount, 2 status from dual union all
select 12 id, date '2024-02-10' orderDate, 45 qty, 456.78 amount, 2 status from dual

However, when I append a comment using double dashes at the end of the first line like this:

select 12 id, date '2024-01-15' orderDate, 25 qty, 148.75 amount, 2 status from dual union all --
select 12 id, date '2024-01-16' orderDate, 33 qty, 267.89 amount, 2 status from dual union all
select 12 id, date '2024-01-17' orderDate, 51 qty, 423.56 amount, 2 status from dual union all

I receive the following error:

ORA-00928: missing SELECT keyword

Is this a recognized issue in SQL Developer, or could it be that I’m overlooking something? Using block comments is a viable workaround, but I’m curious as to why line comments create this complication.

2

Interesting! Try adding a space or text after the double dashes - like -- comment here instead of just --. The empty comment might be confusing SQL Developer about where the statement ends. Does this happen with other union queries or just this one?

3

yeah, this is a known quirk with sql developer. the double dash comment eats the line break after ‘union all’, which confuses the parser. put some text after the – or move your comment to its own line instead of keeping it at the end.

4

The Problem: You’re encountering an ORA-00928: missing SELECT keyword error in Oracle SQL Developer when using line comments (--) at the end of a line containing a UNION ALL clause. Your query works correctly without the line comment.

:thinking: Understanding the “Why” (The Root Cause):

The issue stems from how SQL Developer’s parser handles line comments. The double-dash (--) comment consumes the remainder of the line, including the line break after the UNION ALL. This effectively concatenates the UNION ALL with the subsequent SELECT statement, resulting in invalid SQL syntax. The parser interprets the two as a single, incomplete statement. Oracle then raises the ORA-00928 error because a SELECT keyword is missing to complete this erroneously combined statement. This problem is specific to line comments placed at the end of lines containing UNION ALL and isn’t a general SQL standard issue.

:gear: Step-by-Step Guide:

  1. Relocate or Modify Your Comments: The simplest solution is to either move your line comments to their own line, or add text after the --.

    • Option 1 (Recommended): Move the comment to its own line: This is the most robust and readable approach. Place the comment on a new line following the UNION ALL clause.

      select 12 id, date '2024-01-15' orderDate, 25 qty, 148.75 amount, 2 status from dual union all
-- This is now a separate, well-formed comment
select 12 id, date '2024-01-16' orderDate, 33 qty, 267.89 amount, 2 status from dual union all
select 12 id, date '2024-01-17' orderDate, 51 qty, 423.56 amount, 2 status from dual union all

    • Option 2: Add text after the comment: If you absolutely must keep the comment on the same line, add a space and some text after the --. This separates the comment from the following SELECT statement. However, this is less readable than Option 1.

      select 12 id, date '2024-01-15' orderDate, 25 qty, 148.75 amount, 2 status from dual union all -- Comment text here
select 12 id, date '2024-01-16' orderDate, 33 qty, 267.89 amount, 2 status from dual union all
select 12 id, date '2024-01-17' orderDate, 51 qty, 423.56 amount, 2 status from dual union all

  2. Verify your Query: After making the change, re-run your SQL query in SQL Developer. The error should be resolved.

:mag: Common Pitfalls & What to Check Next:

  • Incorrect Syntax: Double-check for other potential syntax errors in your query, especially around the UNION ALL clauses. Misplaced commas or other syntax errors can also produce ORA-00928.
  • Complex Queries: If you’re working with very long or complex queries containing many UNION ALL operations, consider breaking them down into smaller, more manageable subqueries to improve readability and reduce the chances of similar parsing errors.
  • SQL Developer Version: While unlikely, ensure you’re using a supported and updated version of SQL Developer. Older versions might have quirks in their SQL parser.

:speech_balloon: Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!