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.
SQL Developer Team
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...
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.
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.