msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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 on 09 Oct 07 | Tags: ,


Reader's Comments

  1. |

    That seems so stupid from Oracle, Eddie. Does this apply to all versions?

  2. |

    I do not know if this applies to all versions. We have Oracle Forms version 6, EBS 11.5.10 and a 10gR1 database.

  3. |

    The /…/ style comments are much better if you ever have to dig SQL out of the V$SQL views

  4. |

    [That looks odd - looks like it did something to the asterisks - try again /../]

  5. |

    Gary, right, it’s better. The only disadvantage of the slash and asterisk commenting style is that it requires more typing than the double hyphen.

    By the way, the stupid Markdown syntax ate the asterisks *. Sorry. I used the backslash \ to escape it.

  6. |

    Huh, I’ve never experienced that so thanks for the tip. However, the biggest sin he committed was hard-coding a value instead of referencing the block.field value. Think of the noobs who will see this and do the same. ;)

  7. |

    Matt, thanks for your tip too. I agree with you, hardcoding should be avoided. However, the query above is not the actual query that is used in the form. I just used a simple query here for demonstration purposes and to illustrate the problem that we faced.

  8. |

    So you mean, “Beware of comments in Oracle Forms LOV definitions”? That’s not the same thing as “Beware of Comments in SQL”.

  9. |

    William, sure, if you want to be picky :) I can be even pickier: “Beware of double hyphen style comments in Oracle Forms LOV definitions”.

    But, I can think of at least a couple of situations that have nothing to do with LOVs in Oracle Forms and where double hyphen style comments inside SQL can cause problems.

    While debugging ColdFusion web applications, the SQL used in these applications is logged, usually as a long string with no line breaks. Having a double hyphen comment in the middle of it will cause a problem when trying to execute it as is. I usually need to edit the SQL first, which is a hassle. I think this is indirectly related to Gary’s comment above.

    The second situation is, well, rather simple. For example:

    SQL> select * from dual;
    
    D
    -
    X
    
    SQL> select * -- comment here from dual;
    select * -- comment here from dual
                                     *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected
    
    
    SQL> select * /* comment here */ from dual;
    
    D
    -
    X
    

    Powel also shared an interesting thing about comments in SQL*Plus.

  10. |

    Well, duh ;)

    Though it did remind me of the colleague who couldn’t work out why he got a “missing expression” error with something like this:

    EXECUTE IMMEDIATE ‘SELECT –+ FULL(e) ‘ || ‘ COUNT(*) ‘ || ‘FROM emp e ‘ || ‘WHERE e.empno = 42′ INTO v;

  11. |

    …only with the line breaks I posted left in…

  12. |

    [...] Awad reminds us, beware of comments in SQL. Beware also of SQL injection. It is no laughing matter, I assure you. Nonetheless, Andrew Clarke [...]

  13. |

    and what about this :)

    http://coskan.wordpress.com/2007/04/23/oracle-idiosyncrasies-2/

  14. |

    Good one Coskan. By the way, Gary and William, I have fixed the formatting in the comments, so the line breaks and the /* */ are preserved.