DISQUS

Eddie Awad’s Blog: Beware of Comments in SQL

  • Albaraha · 2 years ago

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

  • Eddie Awad · 2 years ago

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

  • Gary · 2 years ago

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

  • Gary · 2 years ago

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

  • Eddie Awad · 2 years ago

    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.

  • Matt · 2 years ago

    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. ;)

  • Eddie Awad · 2 years ago

    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.

  • William Robertson · 2 years ago

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

  • Eddie Awad · 2 years ago

    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:


    <pre>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
    </pre>

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

  • William Robertson · 2 years ago

    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;

  • William Robertson · 2 years ago

    ...only with the line breaks I posted left in...

  • coskan · 2 years ago
  • Eddie Awad · 2 years ago
    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.