4 Replies Latest reply on Feb 19, 2014 9:33 AM by Arpod

    SQLDev4: Find DB Object does not work when using mixed case strings

    PaoloM

      Hello,

      I am using the latest version of SQL Developer (Version 4.0.0.13 Build MAIN-13.80) and I would like to report a possible bug I have recently found and that I can replicate at will.

      Since I am working mostly with the eBusiness Suite (oracle db 11.2.0.3), I use very often the Find DB Object window to find the objects I need among the thousand available and I noticed that whenever I enter a mixed case string in the search box, no objects are found, while if the case of the same search string is either all uppercase or all lowercase, I can find the objects I am looking for.


      To replicate you can try the following when connected to the SCOTT schema:

      1) open the Find DB Object window and choose a database connection;

      2) enter the string %ive%ais% and press Go: the search will retrieve the GIVE_RAISE procedure;

      3) enter the string %ive%AIS% and press Go: the search will NOT retrieve anything;

      4) enter the string %IVE%AIS% and press Go: the search will retrieve again the GIVE_RAISE procedure;

       

      I think this issue was already reported in another thread during the EA releases (see EA4.2  - Find Database Object (Mixed Case)), but it seems the issue was not fixed.

      Is it possible to raise a bug for it?

       

      Thanks,

      Paolo

       

      Messaggio modificato da PaoloM

        • 1. Re: SQLDev4: Find DB Object does not work when using mixed case strings
          Gary Graham-Oracle

          Hi Paolo,

           

          Apparently at one point during development of 4.0 Find DB Object worked the way you want since there was bug

          Bug 16077541 - MIXED CASES FOR NAME FIELD IN FIND DB OBJECT

          that discussed whether or not the user should be required to double quote mixed case names when entering the search string.

           

          Jeff's reply in the other discussion you reference occurred months after that bug, so this must have been flip-flopping back and forth.  Since I don't really know the back-story on this, and Find DB Object improved so dramatically with the advent of 4.0 release, I think someone else should decide if a bug needs to get logged.

           

          Regards,
          Gary

          SQL Developer Team 

          • 2. Re: SQLDev4: Find DB Object does not work when using mixed case strings
            PaoloM

            Gary,

            thanks for your answer. I agree that the Find DB Object has improved a lot, as I use it very often and I remember that the one present in version 3 was quite slow (I had a discussion with Vadim about it). My only minor gripe with it is that there is no keyboard shortcut to open it/bring focus on it.

             

            Anyway, I think that my example was not very clear, as I did not actually enter the double quotes in the search field. I have updated the original post to make it clearer. Could you please retry the test case and check if the Find DB Object window behaviour you see is really what is expected?

             

            Moreover, I believe that if the object has a name in mixed case then it should be indeed mandatory to specify the double quotes (but this is not the case with the test case I proposed).

             

            Finally, if you decide to keep the current behaviour (that is mixed case search strings will not return any match if the user is looking for a normal upper case named object), then IMHO you should document better the current behaviour of the Find DB Object window in the online help. Just my 2 cents...

             

            Thanks,

            Paolo

            • 3. Re: SQLDev4: Find DB Object does not work when using mixed case strings
              Gary Graham-Oracle

              Hi Paolo,

               

              After further review of Find DB Object behavior, I really do not find much fault with it:

              • If the user explicitly double-quotes the search string, the double-quotes are stripped off.
              • If the user enters a mixed case search string, the mixed case search string is passed through "as-is".
              • Otherwise, the search string (either all uppercase or all lowercase) is upper-cased before being passed on.

               

              So in performing comparisons against user-specified object identifiers in database metadata, it works as intended.  If an identifier was created as mixed case with a double-quoted name, it is only found if the user entered a mixed case search string.  Otherwise everything is upper-cased and matched accordingly.  This makes perfect sense since, in general, PL/SQL is not a case-sensitive language, and the standard Oracle recommendation is to avoid mixed case names.

               

              For your  %ive%AIS% test case to return anything, you would have had to define the GIVE_RAISE procedure with a name like Give_RAISE or something similar.  And, of course, more documentation always helps.


              The only flaw I can see is the case where an object identifier is created as all lowercase with a double-quote name.  Since lowercase search strings always get upper-cased, such an identifier could never be matched in a search.  


              -Gary-   

              • 4. Re: SQLDev4: Find DB Object does not work when using mixed case strings
                Arpod

                Hi Gary.

                 

                Sorry to enter your discussion like that, but are Find DB Object functions used for "open declaration" as well? Because, as I described in SQL Developer 4.0: 'Open Declaration' bugs, it seems that it can't handle mixed case names (which are generally legit inside packages or procedures or functions) because of that.

                 

                As for "Find DB Object" behavior itself, as you mentioned it, PL/SQL is generally not case-sensitive, so I actually would expect to find GIVE_RAISE if I query for %ive%AIS% . If I would search for give_RAISE, I would actually enter "give_RAISE" in the query - with double quotes.