12 Replies Latest reply on Jan 9, 2017 3:48 PM by rp0428

    How to omit schema/user from auto-completion of table name?

    3365751

      There is a old thread about this topic, it is still not solved: How to omit schema/user from auto-completion of table name?

       

      When I write "select * from t_" and click CTRL + SPACE a dropdown pops up showing me all table names from all users starting with "t_" whereas I want to see only suggestions from the connected user.

       

      All other DB tools which I know don't behave like this. They display only the table names of the currently connected user when auto-complete is used.

       

      This really annoys me and my colleages in SQL Developer. Especially if you have a database server with lots of users then auto-completion shows a lot of suggestions you don't want to see, making auto-completion virtually unuseful.

       

      How can this be solved? Or is there a good workaround (other than using another DB tool)? Or is there a SQL Developer configuration for this? Or is it planned to be implemented as a feature?

        • 1. Re: How to omit schema/user from auto-completion of table name?
          Gary Graham-Oracle

          In general, the auto-completion feature shows you what you have the privileges to see.  For example, if your user has been granted...

          1. The role SELECT_CATALOG_ROLE, or

          2. The system privilege SELECT ANY DICTIONARY

          ...then you will see lots more than what the connected user owns.  The exact queries used may vary by SQL Developer release, but to see what those are check View > Log and see the Statements tab.

          • 2. Re: How to omit schema/user from auto-completion of table name?
            thatJeffSmith-Oracle

            >>with lots of users then auto-completion shows a lot of suggestions you don't want to see, making auto-completion virtually unuseful.

            From what I can ascertain, the ones you DO want to see, are always listed first, so how is it unusable?

             

            schemas.png

             

            Granting that's unusable, just do this:

             

            select * from hr.e <ctrl+space>

             

            You'll get EMPLOYEES

                           EMPLOYEES_COPY2

             

            Pick one.

             

            What sqldev inserts into the editor is JUST EMPLOYEES vs HR.EMPLOYEES.

             

            So you get the best of both worlds. This seems much more flexible than the other tools out there.

             

            You don't say which version you're using. I'm using v4.2 EA1.

            • 3. Re: How to omit schema/user from auto-completion of table name?
              3365751

              That first sounded like a workaround to disable that role for me, but in other user cases I might need it by using a query for it (and not as auto-completion).

              As I said, all other DB tools I know don't display me all the other users's tables in auto-complete. I connect with the same user in PLSQL Developer for instance, and it limits auto-complete suggestions just to the connected user's tables (and also views maybe, not sure about that last thing).

              • 4. Re: How to omit schema/user from auto-completion of table name?
                3365751

                It does not behave that way for me. I am using version 4.1.5. If it would behave that way for me, it would be better, still not ideal in my eyes, because it is confusing in most cases.

                 

                When there is no table starting with "t_" then I don't want any suggestions in auto-complete because there are no tables in the scheme I am connected with.

                 

                If I write "select * from t_" and press CTRL + SPACE then actually no of my tables is displayed in the dropdown but tables in the many other schemes starting with "t_". So it is even worse the behavior than the one which you have which would be not ideal but bearable in a way.

                 

                Maybe there was a change in the version you mentioned, although I think it is unlikely that it is because of the small version difference.

                • 5. Re: How to omit schema/user from auto-completion of table name?
                  thatJeffSmith-Oracle

                  in version 4.1.3 the pick list looks the same

                   

                  schemas413.png

                   

                  but in v4.1 if you type select * from hr.e and then pick something from the local schema, the schema prefix remains. That's not ideal, hence the change for v4.2.

                   

                  We have another update with a bunch of bug fixes and new features being added for v4.2 EA2 (ea = early adopter = beta), so stay tuned for that

                  • 6. Re: How to omit schema/user from auto-completion of table name?

                    but in other user cases I might need it by using a query for it (and not as auto-completion).

                    Well other people DO WANT IT for auto-completion. Why should they have to type in the whole name to see the list of objects?

                    As I said, all other DB tools I know don't display me all the other users's tables in auto-complete.

                    That is a deficiency in those 'other DB tools. If you don't know the name you need the list. If you know the name type in as much as you know and the list will show the matches that begin with that.

                     

                    How is ANY tool supposed to know what object, of ALL objects you have access to, is the one you want?


                    auto-complete. I connect with the same user in PLSQL Developer for instance, and it limits auto-complete suggestions just to the connected user's tables (and also views maybe, not sure about that last thing).

                    Why shouldn't you be able to auto-complete objects for other schemas?

                     

                    The main purpose of auto-complete is to help people find the object they want. You can always find your object under your user in the nav tree and get the name from there.

                     

                    Functionality has to try to do the most good for the most people. It's always a trade-off. And once a choice has been made there has to be some pretty good justification for expending the amount of effort and resources it would take to change it. Especially when those resources are needed for bug fixes and adding new functionality.

                    • 7. Re: How to omit schema/user from auto-completion of table name?
                      3365751

                      It can be made configurable so that the auto-complete suggestions are limited to local schema depending on what the user sets in the tool preferences.

                       

                      Or it could be done similar like in IDE tools for object oriented languages. Usually you only see the methods of the class if you click for instance CTRL + F12 in IntelliJ Idea. If you press again CTRL + F12 or click on the check box "Show inherited members" in the dropdown view then you also see the inherited methods from the super classes. That kind of solution could be applied also to auto-complete in SQL Developer. If a user clicks twice CTRL + SPACE only then the non-local tables would be displayed in the dropdown.

                       

                      I am not suggesting something as smart as Google auto completion (although IntelliJ as far as I know aspire an intelligent auto-complete behavior). Like a car driver I would like to modify the seat so that it fits for me and not have to drive in an uncomfortable standard position, meant as an analogy for my suggestions.

                      • 8. Re: How to omit schema/user from auto-completion of table name?
                        3365751

                        I checked with the behavior with version 4.2.0.16.356.1154, it has the same disappointing un-smart and user-unfriendly behavior.

                         

                        Hopefully there will soon be an improvement.

                        • 9. Re: How to omit schema/user from auto-completion of table name?
                          thatJeffSmith-Oracle

                          What are you seeing in 4.2? Screenshots are always welcome.

                          • 10. Re: How to omit schema/user from auto-completion of table name?
                            Gary Graham-Oracle

                            From your comments...

                            It can be made configurable so that the auto-complete suggestions are limited to local schema depending on what the user sets in the tool preferences

                            only a configuration setting to limit the pick list to local schema objects will satisfy you.  However Jeff is saying...

                            From what I can ascertain, the ones you DO want to see, are always listed first, so how is it unusable?

                            So you probably need to show via screenshot that his assertion is not true to gain any traction in this discussion.

                            • 11. Re: How to omit schema/user from auto-completion of table name?
                              3365751

                              It is not feasible to provide screenshots, since I am in the financial business, they would not like me to expose table names to the public.

                              I will think about how to explain the problem and solutions in another way and then post something here hopefully.

                              • 12. Re: How to omit schema/user from auto-completion of table name?

                                Just create a test schema and test objects to reproduce the problem and provide screenshots from that.