13 Replies Latest reply on Apr 18, 2017 4:57 PM by Jim 13131

    4.2 Error with Single line comment ending with semi colon

    Sanal Krishnan Narayanan

      Hello,

       

      Was trying out the released 4.2 version. On selecting all the lines in the below query and hitting F9 we get an error :

      select * from dual
      where 1=1
      --and 2=2;
      and 3<>3;
      

       

      Error starting at line : 4 in command -
      and 3<>3
      Error report -
      Unknown Command
      

       

      4.2Singlelinecomment_error3.jpg

       

       

      Without selecting the lines, and with the cursor on the first line, on hitting F9 it would ignore line 3 & 4 .

      4.2Singlelinecomment_error.jpg

       

      Doing the same with F5 would lead to the same error:

      4.2Singlelinecomment_error2.jpg

        • 1. Re: 4.2 Error with Single line comment ending with semi colon
          Gary Graham-Oracle

          You do not say if you have used a prior version of SQL Developer as a basis for comparison.

           

          The behavior in 4.2 differs, for example, from that of 4.0.3, but this is due to the advent of SQLcl and the common code base it shares with SQL Developer, and the goal of being able to run SQL*Plus scripts in SQLcl with no or minimal change as SQLcl evolves.  Basically, I believe the behavior you are seeing is the new expected behavior.

          • 2. Re: 4.2 Error with Single line comment ending with semi colon
            Adric

            Generally I'd consider maintaining compatibility with SQL*Plus to be a very worthwhile goal. That particular feature/quirk, however, really needs to die in a fire, get run over by a truck, and have its charred remains buried at sea.

             

            Just my opinion, of course.

            • 3. Re: 4.2 Error with Single line comment ending with semi colon
              Gary Graham-Oracle

              The principle used in the SQL Developer GUI is simple...

               

              1) Run Script (F5) runs everything in the worksheet unless you highlight a specific portion of its content.

              2) Run statement (F9) runs only the current statement (automatically determining end-of-statement) unless you highlight more.

              3) In addition, for (2), if multiple statements are highlighted, each statement's query result is auto-pinned despite the preference settings.

               

              Perhaps someone else will comment further, perhaps the rule for automatically determining end-of-statement for F9 could be changed, but I would guess the F9 F5 behavior will remain consistent with SQL*PLus behavior.

              • 4. Re: 4.2 Error with Single line comment ending with semi colon
                Sanal Krishnan Narayanan

                Hello Gary,

                I did not compare the behaviour with prior versions. I was typing out a query on a sql worksheet, and had to comment out the last line  to test something, and found it weird that with the semicolon at the end, it would throw this error.

                 

                Removing the semicolon at the end, or moving the semicolon to somewhere else other than at the end causes it to work as per my expectation - that the line is commented out with a  "--" and whatever is in it after the "--" should not be considered for anything, semicolon or not.

                 

                If this is the new behaviour, no worries .

                • 5. Re: 4.2 Error with Single line comment ending with semi colon
                  Gary Graham-Oracle

                  One additional note:  If the semi-colon is the final non-blank character in the "--" comment line, then it does count as end-of-statement.

                  • 6. Re: 4.2 Error with Single line comment ending with semi colon
                    thatJeffSmith-Oracle

                    I'd agree with you, but if we 'fixed' this, folks would complain that the scripts they hand crafted in SQL Developer completely BROKE in their 'production SQL*Plus' deployments.

                     

                    comment_only.png

                    • 7. Re: 4.2 Error with Single line comment ending with semi colon

                      I'd agree with you, but if we 'fixed' this, folks would complain that the scripts they hand crafted in SQL Developer completely BROKE in their 'production SQL*Plus' deployments.

                      True - an unfortunate consequence of the sql*plus implementation having a bug and not conforming to the ansi/iso standard for the "--" comment style. The risk/reward is far too high for sql*plus to fix that bug now I would imagine.

                      • 8. Re: 4.2 Error with Single line comment ending with semi colon

                        Just to clarify

                         

                        1. the reported error is line 4 because that line by itself is NOT a valid statement.

                         

                        2. sql*plus has a bug in that it does NOT conform to the ansi/iso standard for the "--" style comments. That standard states that ALL characters after two or more successive hyphens are comments until the new line. A semi-colon anywhere after the leading hyphens would be part of the comment per the standard

                         

                        3. sql developer, by design, is conforming to the (buggy) sql*plus implementation and that compatibility is more important than fixing the bug in sql dev.

                         

                        4. sql developer accepts the first two lines only and will execute them just fine without any terminator at all - even though that will NOT work in sql*plus

                         

                        Just one of the quirks you need to live with when compatibility with another produce is the goal. Technically sql dev could provide a preference so that users could choose, or not, compatibility but hardly worth the limited benefit for such an obscure use - especially when the issue can, and should be, easily avoided.

                        • 9. Re: 4.2 Error with Single line comment ending with semi colon
                          Buntoro

                          Hi Jeff,

                           

                          Why not put it into Preferences?  i.e Query does not terminate on comment with semi-colon.

                           

                          Lots of developers would use SQL Dev for making query and most of the times we add -- for commenting in or commenting out during query testing.

                          And it was easier since commenting a line in the middle of the query would not destroy the whole effort.

                           

                          Can you imagine having something like this

                          select *
                          from   table_a, table_b, table_c
                          where  a.id = b.id
                          and    b.id = c.id
                          and    a.value = 5
                          -- and    b.value = 2;
                          and    b.value = 1;
                          

                           

                          Previously the query was about to filter b.value as 2,

                          And the developer during testing comment it, but SQL Dev completely ignore the new b.value filter as 1.

                          In the more complex situation where the developer would make lots of query alteration, would suffer from something like this.

                           

                          I, myself, would prefer how the old SQL Dev works.

                          Still Preferences would be a good option.

                          If you would like to maintain the SQL*Plus compatibility in SQL Dev, let the option to be untick / uncheck by default.

                          As long as we can modify, that would be fine.

                           

                          Regards,

                           

                          Buntoro

                          • 10. Re: 4.2 Error with Single line comment ending with semi colon
                            Sanal Krishnan Narayanan

                            Hello,

                             

                            My daily development tool is Quest SQL Navigator, and it ignored the commented out line. Like I mentioned initially, I was testing changes to a query and noticed this quirk, and then confirmed the quirk with the simple query.

                             

                            A "preference" to provide an option to maintain SQL * Plus compatibility is a good idea ! 

                             

                            On the topic of maintaining compatibility with SQL*Plus, if sqlcl could be as fast in opening up as sql*plus, it would be of great help !

                            • 11. Re: 4.2 Error with Single line comment ending with semi colon
                              thatJeffSmith-Oracle

                              >>On the topic of maintaining compatibility with SQL*Plus, if sqlcl could be as fast in opening up as sql*plus, it would be of great help

                              Startup time for SQLcl will be limited by the JVM, vs SQL*Plus which is a natively compiled application.

                               

                              Startup time for SQLcl is fast compared to the full GUI of SQL Developer, but will probably never be as fast as SQL*Plus.

                               

                               

                              • 12. Re: 4.2 Error with Single line comment ending with semi colon
                                Günter

                                Works with F5 and F9 (with unexpected result) - WHERE is terminated by first occurence of semicolon.

                                CREATE OR REPLACE VIEW dual_y AS

                                    SELECT *

                                    FROM dual

                                    WHERE 1 = 1

                                --and dummy = 'X';

                                      AND dummy   = 'Y';

                                • 13. Re: 4.2 Error with Single line comment ending with semi colon
                                  Jim 13131

                                  I agree with the comment above looking for an option for this. I don't want compatibility with a command line tool that I avoid using (SQLplus) - I want to take advantage of the windowed GUI environment.

                                   

                                  Also the commented out ; is displayed in the grey commented out color in SQL Devloper instead of the black color of a non-commented out ;  - very misleading.

                                   

                                  Jim