14 Replies Latest reply on Jan 13, 2017 4:54 PM by Sentinel

    [4.2EA] Comments ending in a semicolon is not handled properly

    frankrwahl

      No problem in 4.1.3, but this SQL that includes a comment ending in a semicolon,

      is not handled correctly in 4.2ea:

       

      select * 
      from dual
      -- The problem is this semicolon;
      where dummy = 'Y';
      

       

      The result is either 'X' (which is wrong) or an error message.

        • 1. Re: [4.2EA] Comments ending in a semicolon is not handled properly
          Dermot ONeill-Oracle

          Hi Frank,

           

          Thanks for downloading SQL Developer 4.2(EA) and for the feedback.

           

          We have been working hard to make SQLcl and SQL Developers Worksheet match SQL*Plus.

          We want SQLcl & SQL Developer to be able to run SQL*Plus statements and scripts exactly as SQL*Plus would.

          In the past SQL Developer may have been more "loose" with how statements were recognized and executed.

           

          However I can see how this could be a problem if you have scripts written and used in previous releases of SQL Developer and in 4.2(EA) they do not work.

          I can also see how it made previous versions of SQL Developer a little more forgiving. But that could lead to problems down the road if someone was to run your script with SQL*Plus.

           

          SQL*Plus equivalence

          PROS:

          A script written for SQL*Plus will run exactly the same in SQLcl and SQL Developer.

          A script written in SQLcl and SQL Developer will run exactly the same with SQL*Plus. This is important if you publish or give a script to someone else who may run it with SQL*Plus. (Note obviously you could not expect SQLcl new commands like ALIAS to work in SQL*Plus)

           

          CONS:

          SQL Developer and SQLcl are little more "exacting" on what is recognized and executed.

           

           

          I would like to hear your thoughts.

          Should SQL*Plus standard be enforced?

           

          Regards,

          Dermot.

          SQL Developer Team.

          • 2. Re: [4.2EA] Comments ending in a semicolon is not handled properly
            frankrwahl

            Hi Dermot,

             

            We use SQL*Plus (not SQLcl yet) for deployment of our version controlled code files, ddl scripts, etc. onto our test/prod-servers, and I would definitely prefer the SQL*Plus standard to be enforced all over. I have experienced that SQL Developer runs my script successfully, but SQL*Plus not.

             

            Consistency is good. I can live with that "SQL Developer and SQLcl are little more "exacting" on what is recognized and executed".

             

            Keep up the good work,

            Frank

            • 3. Re: [4.2EA] Comments ending in a semicolon is not handled properly
              B.Delmée

              > I would like to hear your thoughts.

              > Should SQL*Plus standard be enforced?

               

              Hi Dermot, I have been surprised (and slightly annoyed at first) by this change of behaviour between 4.1 and 4.2, too (already mentioned in another thread).

               

              However I think you made a pretty good case of explaining the rationale behind this change, which is not gratuitous.

              Compatibility with sql*plus is desirable, even more so for sqlcl which shares code with sqldeveloper, so this shouldn't be considered a regression.

              • 4. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                Sentinel

                While compatibility with SQL*Plus is an understandable goal, should your compatibility really include this quirk that I would argue is more of a bug in the way SQL*Plus operates?

                 

                A trailing comment should be a comment all the way to the end of the line, without any "unless followed by a terminating semicolon" provisos.

                 

                When I'm working in a SQL Worksheet I frequently use it as a scratchpad, writing and modifying SQL statements until they are just right.  The process may involve commenting and uncommenting lines of code to compare results.

                 

                This particular quirk will make it more difficult  to try out various pieces of code that contain terminating semi colons.

                 

                One frequent example where I comment and uncomment code with terminating semi colons is when working with complex subfactored queries where I'll frequently add a

                select * from <sfq>;  

                just after a subfactored query to test changes to that block of code, then quickly comment out the select * to validate the change in the over all query often by just using CTRL-/ to toggle line comments.  If SQL Dev starts treating semicolons at the ends of comment lines as statement terminators, this development process will no longer work.

                • 5. Re: [4.2EA] Comments ending in a semicolon is not handled properly

                  SQL Developer and SQLcl are little more "exacting" on what is recognized and executed.

                   

                   

                  I would like to hear your thoughts.

                  Should SQL*Plus standard be enforced?

                  IMHO - yes. Doing so provides substantial benefits to the dev team and to the customer.

                   

                  1. it provides a ready-made set of 'requirements'.

                   

                  Often the most difficult parts of ANY project are adequately defining the requirements and conducting the necessary tests for validating that those requirements have been met.

                   

                  Enforcing the sql*plus standard provides both a ready-made set of 'minimal' requirements for Sql Dev as well as a tool (sql*plus itself) that can validate your work.

                   

                  2. it provides additional incentives for people to try, and ultimately switch to, Sql Dev.

                   

                  In my experience the two main reasons someone tries/uses a new tool are 1) it does their current task better/easier/faster and 2) it does some things that the current tool doesn't do.

                   

                  Even with 25+ years in the industry I could not recommend anyone SWITCH to Sql Dev if it can't do what their current tool (often sql*plus) does. Because the obvious question would be: 'the new tool does NOT do X so what tool should I use to do X'?

                   

                  3. It makes it far easier to PROPERLY extend/add new/better functionality if you always have that 'sanity check' of compatibility.

                   

                  That 'sanity check' is the always lurking question: 'if this proposed new feature is so good why wasn't it added to sql*plus?'.

                   

                  You could always add compatibility switches if you wanted/needed to extend existing functionality in a way that wasn't compatible. But only if that made sense in terms of the future direction you want the product to take.

                   

                  One 'simple' way to start heading in that direction for the command-line app is designate a custom 'comment' line that specifies the app version

                  REM SQLCL-version n.n.n.n

                  That  will allow your future releases to know what version the script was written for and act accordingly.

                  • 6. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                    Sentinel

                    If this were a configurable compatibility feature I'd be happier, even if it were enabled by default, then I could disable it to better fit my use of the tool.

                     

                    I don't find the behavior to be natural or intuitive in an interactive development environment, though it may make limited sense in a line editor oriented development tool like SQL*Plus where interactively terminating a line comment with a semicolon would result in immediate execution of the SQL statement.  However, in file editor or IDE based development where that immediate feedback is lacking, the behavior is not optimal.  Further with syntax highlighting showing the line as a comment the behavior is not expected.  Even if the trailing semicolon wasn't formatted as a comment visually noticing the formatting change of a single character at the end of a line may not be particularly easy to do.

                     

                    Since it is a compatibility issue for SQL*Plus I'd rather have the ability to toggle it on and off, both as a configuration setting (possibly along with other compatibility features), and as switch on the SQL Worksheet tool bar as a check box to enable all compatibility options (enabled) or just those selected in the configurations settings (disabled).  Additionally if the editor could highlight compatibility issues in the right hand gutter as compatibility warnings, that would be useful.  Alternatively having a 2nd Run Script option to run the code in strict compatibility mode would be a good alternative.

                    • 7. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                      HarbourGhost

                      To throw the question a bit wider....

                       

                      In the old days when SQL*Plus started (pre PL/SQL), all statements (DDL and DML) could be terminated in a semi-colon.

                      When PL/SQL came in, a few keyword (DECLARE, BEGIN) or a subset of DDL statements (CREATE PROCEDURE/FUNCTION/TRIGGER) would always signify a block of PL/SQL which had to include semi-colons as part of the internal syntax.

                       

                      In the latest version of the database, we have the option of including PL/SQL under a WITH clause in both DML (SELECT, UPDATE etc) and some other DDL (CREATE VIEW, CREATE TABLE AS...SELECT etc). For the first time, we are dealing with statements which may or may not include semi-colons as part of internal PL/SQL syntax. We don't know what changes may come in future versions of the database that might complicate that parsing further (perhaps Javascript stored procedures or some form of JSON literals)

                       

                      SQL*Plus supports the SET SQLTERMINATOR command so has the capability of disabling it or switching to a less confusing character for a particular script. Down the line they have the option of having the SQL terminator to be disabled or set to a different character if use of the semicolon becomes problematic.

                       

                      The SQL Terminator is a major risk point. Having execution fire prematurely on a DELETE would be horrendous

                      delete from transactions

                      -- how about this;

                      where cancelled = 'Y';

                       

                      If you are going to make SQL Developer/SQLcl compatible with SQL*Plus, then it needs to be 100% right. I think, at the minimum, you'd need SET SQLTERMINATOR OFF support so that a coder can be sure that a script will never jump the gun and execute a partial statement.

                      • 8. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                        Sentinel

                        HarbourGhost I agree interpreting a the SQLTERMINATOR character at the end of a comment line as the SQLTERMINATOR does pose a risk, but this is the way SQL*Plus currently operates.  Which is another good reason why I'd argue that it's a comaptibility point that perhaps shouldn't be brought forward, but perhaps should be pushed back at SQL*Plus as an enhancement to that product.  But if it is brought forward into SQL Dev, it should be configurable such that it can be disabled.

                        • 9. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                          B.Delmée

                          HarbourGhost wrote:

                           

                          The SQL Terminator is a major risk point. Having execution fire prematurely on a DELETE would be horrendous

                          delete from transactions

                          -- how about this;

                          where cancelled = 'Y';

                           

                          Yikes! scary.

                           

                          OTOH making strict sql*plus compatibility optional (in sqldev via a preference, perhaps as a CLI-switch for sqlcl) would be confusing for newbies and a maintenance burden.

                           

                          If both modes were supported, perhaps "execute script" (F5) could default to strict compatibility whereas "execute statement (Ctrl-Enter) could be more forgiving/saner ?

                          • 10. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                            Liam D

                            Hi Dermot thanks for the explanation.

                             

                            While maintaining backward compatibility is a noble endeavour, I feel that it is being taken too far here.

                             

                            Oracle are crippling future development to maintain compliance with a tool that was developed in much 'simpler times'.

                             

                            If SqlCl and SqlDeveloper are the Oracle standard interfaces moving forward then I see huge benefit in maintaining compatibility BETWEEN them (hopefully by reusing the code base), but at some point we have to break away from our historical shackles to embrace the benefits available through progress.

                             

                            IMO Oracle's efforts would be better placed in writing an upgrade tool for SQL*Plus scripts and having that available alongside your newer, modern use case, tools.

                             

                            Obviously this will require some communication to your current user base, but surely that is an easier job than continually explaining the internal quirks to every new user that comes along (and potentially putting them off 'this complicated Oracle thing' in the process)

                            • 11. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                              Dermot ONeill-Oracle

                              Hi All,

                               

                              Really appreciate this feedback.

                              We'll be discussing this shortly within our team so its great to have a users points of view.

                               

                              Thanks.

                              Dermot

                              SQL Developer Team.

                              • 12. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                                B.Delmée

                                HarbourGhost's example gave me pause. Maybe the focus of sqlcl is to be a better interactive tool, not necessarily a perfect re-implementation of sql*plus, warts and all.

                                 

                                In all likelihood; sql*plus is not going away. It is there and relied upon by many users, both for interactive work and perhaps more importantly, for scripting. Should sqlcl strive to supplant it in that role? With the kind of memory footprint java imposes, i am not even sure this is desirable for automated scripts On my win7 64 bits laptop here is the rough memory consumption i am seeing:

                                 

                                ToolIdle (MB)After running a simple query (MB)
                                sql*plus3233
                                sqlcl230320
                                • 14. Re: [4.2EA] Comments ending in a semicolon is not handled properly
                                  Sentinel

                                  There is a simple workaround, add a trailing line comment after the semicolon, so this:

                                  delete from transactions

                                  -- how about this;

                                  where cancelled = 'Y';

                                  becomes:

                                  delete from transactions

                                  -- how about this; --

                                  where cancelled = 'Y';

                                  and no longer deletes all the table contents.

                                   

                                  However, I still don't like this functionality even if it's the way SQL*Plus works.  It can have potentially severe unintended consequences.