Beware of Comments in SQL

In SQL and PL/SQL there are two comment styles you can use, single-line and multi-line:

• Multi-line comments begin with a slash and an asterisk (/*) and end with an asterisk and a slash (*/). the comment can span multiple lines.

• single-line comments begin with -- (two hyphens) and end with a line break. The comment text cannot extend to a new line. So, a double hyphen anywhere on a line turns the rest of the line into a comment.

The turns the rest of the line into a comment can sometimes cause unexpected behavior. My coworker was working on a Oracle form. He created an LOV based on a query similar to this (not the actual query, modified for simplicity):

select employee_id, first_name
from employees
where manager_id = 100 -- The king
and job_id = 'ST_MAN'

The above query returned 5 rows when executed in SQL*Plus. However, when bringing up the LOV in the form, there were 14 records. Where did the extra 9 rows come from? After turning trace on and analyzing the trace file, he noticed that somehow the above query was passed to Oracle like this:

select employee_id, first_name
from employees where manager_id = 100 -- The king and job_id = 'ST_MAN'

Which means that whatever is after the -- was treated as a comment and, as a result, was not executed. The where clause was effectively truncated which explains why he was getting 14 rows instead of 5. The fix was simple:

select employee_id, first_name
from employees
where manager_id = 100 /* The king */
and job_id = 'ST_MAN'

So, what have we learned? We learned to avoid commenting using two hyphens and instead use the slash and asterisk /* */ commenting style inside SQL statements.

Filed in Oracle, Tips with 14 Comments | Tags: gotcha, sql

---