9 Replies Latest reply on Jan 27, 2015 6:02 PM by Cuauhtemoc Amox

    Forward slash in SQL Developer

    2844885

      Hi,

       

      I'm a bit puzzled by the fact that the forward slash does not raise an error in a SQL Developer script.

      For instance:

       

      select * from dual;

      /

       

      would result in that select being executed twice if executed from SQL Plus, but only once when executed from SQL Developer.

      I would think that the forward slash is considered as a comment sign, given the fact that

       

      select * from dual;

      / this should not be a comment

      select * from dual;

       

      works totally fine from SQL Developer.

      IMHO either it should take the "/" as SQL Plus (recall last executed command) or just throw an error. Ignoring it can lead to confusion.

       

      Regards,

      Roberto

        • 1. Re: Forward slash in SQL Developer
          thatJeffSmith-Oracle

          in the SQLDev UI it's treated only as a statement delimiter, since there's no buffer per se in the GUI unlike what you have in SQL*Plus, a command line interface

          • 2. Re: Forward slash in SQL Developer
            2844885

            Thanks for your quick answer Jeff.

             

            I understand, and maybe this should have not been a question but a "caveat".

            It has lead to issues in our case, and that's why I posted it.

            Our developers delivered a script with something like:

             

            insert into abc values (1,2,3);

            / this is bad comment

            insert into abc values (4,5,6);

             

            (wrongly assuming that "/" was a valid character for inserting comments).

             

            They tested the script using SQL Developer and it worked fine resulting in two different rows inserted into abc.

            They delivered to Prod and DBAs executed the very same script (using SQL*Plus this time) and this time it resulted in 3 rows being inserted (second line re-executes the first one).

             

            Other SQL clients (e.g. SQL Workbench/J) just gives you an error, and I prefer that behaviour than just ignoring the / as SQL Dev does.

            Adding some option to the config allowing us to disable the "use / as a statement delimiter" would be my preferred solution.

             

            Regards,

            Roberto

            • 3. Re: Forward slash in SQL Developer
              thatJeffSmith-Oracle

              This is more process related, but they should be testing their scripts in SQL*Plus if they're going to be ran in SQL*Plus. Or, you should run their scripts in SQL Developer if that's where they are building and testing...

               

              We can look at changing the behavior around the '/', but it would be post 4.1, if ever.

              • 4. Re: Forward slash in SQL Developer
                Cuauhtemoc Amox

                On cases where the tool used to deploy is different to the tool used for development, you should use the deployment tool to test your scripts just to be safe on cases like the one you mention.

                 

                Another recommendation which can help you to avoid inserting duplicates given that it does not seem to be a unique key and that multiple inserts can happen not only by the slash but due to re-execution of the script itself for whatever reason, is to add a not exist selection the same values to be inserted, e.g.

                 

                insert into abc values (1,2,3)

                where not exists ( select 'x' from abc where col1=1 and col2=2 and col3=3 )

                • 5. Re: Forward slash in SQL Developer

                   

                  On cases where the tool used to deploy is different to the tool used for development, you should use the deployment tool to test your scripts just to be safe on cases like the one you mention.

                   

                  +1 good advice!

                  Another recommendation which can help you to avoid inserting duplicates given that it does not seem to be a unique key and that multiple inserts can happen not only by the slash but due to re-execution of the script itself for whatever reason, is to add a not exist selection the same values to be inserted, e.g.

                   

                  insert into abc values (1,2,3)

                  where not exists ( select 'x' from abc where col1=1 and col2=2 and col3=3 )

                  -1 terrible advice!

                   

                  First, with no index it will result in a FULL TABLE SCAN for each row you insert like that.

                   

                  Second, if there should be no duplicates then that requirement should be enforced with a unique constraint or index. Otherwise you suffer AWFUL consequences (see 'First' just above)!

                  • 6. Re: Forward slash in SQL Developer
                    Cuauhtemoc Amox

                    This is likely a one off to seed data (e.g. a standalone insert with constant values vs an insert with dynamic values that runs within a program used on a regular basis).

                    • 7. Re: Forward slash in SQL Developer
                      2844885

                      On cases where the tool used to deploy is different to the tool used for development, you should use the deployment tool to test your scripts just to be safe on cases like the one you mention.

                       

                      Indeed, developers have already been asked to test their scripts in SQL*Plus before deliverying them.

                      Having it "fixed" in SQL Developer would be the ideal option, so developers can use SQL Developer (as the name suggests)

                      • 8. Re: Forward slash in SQL Developer
                        thatJeffSmith-Oracle

                        The two tools are different. This is one of those places where they'l likely continue to be differences. I've noted similar behavior in other Oracle IDEs for what it's worth.

                        • 9. Re: Forward slash in SQL Developer
                          Cuauhtemoc Amox

                          Yes, there is also the thing with code definition (e.g. packages) where an slash is actually required besides the semicolon on SQL*Plus, and developers might miss it as not required by SQLDeveloper.