SQL Developer Trick: DropDown Vs Tree

thatjeffsmith SQL Developer 36 Comments

Tell Others About This Story:

I’m not saying there’s anything wrong per se with SQL Developer’s tree control for navigating your connection objects. As a matter of fact there are a few things to really admire about the tree implementation. Just in case you have NO IDEA what I’m talking about, here is the tree in all its glory.

Can't see the database forest for the tree?

Scrolling can get tedious on large databases. So here’s a few tips for those that are keyboard savvy:

Navigate the tree with the keyboard, type into your objects

Just the first letter will take you to the next tree item of that letter.

Use the Right Arrow key to expand a tree or sub-tree. Jump to tables with ‘T’, then use the RightArrow key to expand the list of tables. Here’s a quick example of how to jump into a another schema, view list.

‘O’, ‘RightArrow’, ‘S’, ‘S’, ‘S’, ‘RightArrow’, ‘V’, ‘RightArrow’, ‘P’

That series of keystrokes allowed me to ‘walk the tree.’ I jumped to ‘Other Users’, dove into that list, typed into the ‘SH’ schema, expanded that, then went into ‘Views’, and jumped to the ‘Profits’ view. It took me much longer to type that up then it took me to actually do the task in SQL Developer.

If you are using SQL Developer and you have not acquired this skill yet, please stop reading now and go jump into SQL Developer. Try it. Try it again. Once mastered, you’ll be much more proficient with the tool and a lot more productive as well.

Maybe the Tree is just ‘too big’ for you

You don’t use Editioning Views, Queue Tables, Crossedition Triggers, Materialized View Logs, Directories, Editions, Application Express, Java, etc. Even with the keyboard ‘tricks’, that’s a lot of stuff to wade through.

Or maybe you just don’t grok the tree, no matter what you try.

Thankfully we have an alternative, and it’s called the Schema Browser.

On your tree list, select your connection and mouse-right-click. Towards the very bottom of that context menu, select ‘Schema Browser.’ This will open a new page in your panel where you see the connections and reports.

The Schema Browser is an alternative navigator for your database connection objects. It gives you a ‘Drop Down’ control for selecting both ‘schema’ and database ‘object type.’

It's all about click and scroll here. None of that funny collapsing and expanding business.

Navigation Via Drop Down Controls

It’s pretty simple really. First click into your schema, user, account, whatever. Then click into the type of object you want to populate the list.

Pick a Schema

Users

Pick an Object Type

Schema Object Types

You can still navigate your lists with the keyboard. All of the object specific context menus are intact. Filters, refresh, and ‘new’ buttons are available to the right of the object drop down control with a little ‘down arrow’ looking button. You will still use the Connections panel to manage your connection to that database.

And if you get bored of this view, you can always close it and go back to the Tree. Or use both. I don’t care really so long as you find something that works for you!

Tell Others About This Story:

Comments 36

  1. “Thankfully we have an alternative, and it’s called the Schema Browser.”

    Holy fuck. How did I not know about this? This makes SQL Developer about 1000 times more usable!

  2. New SQLDeveloper user here.
    The Schema Browser looses the search capability that the Navigator posses.
    In the Navigator I can click on tables and then start typing a table name and it will go there.
    It would be nice if you could do that in the Schema Browser.
    Also borrow a page from Toad and put a filer on the Schema Browser.

    Other things:
    – Is there any way for me to adjust the code formatting style?
    – When doing a search on database objects (say a column name), I can see the results on the right. If I click on an item, it will open the view that shows the columns, data, constraints, etc. However, it would be nice if the cursor was positioned on the item I clicked.
    – On the same search on database objects, why is Column not an object type? If I look for a column clicking on All Objects Type it does not find it. I have to click on Columns. Why the extra distinction?
    – On the same search on database objects. Is there a way that the results can be showed by object type but that I would need to drill down to see the details objects? For example it would say TABLE. But to see which table I would need to click on the + sign.

    Keep up the good work!

    1. thatjeffsmith Post
      Author

      it’s there, just look down for the quick filter, and click right to get to the more advanced filtering

      Yes, you can adjust code formatting style – that’s in the preferences.

      Click, nav, open search stuff – it’s supposed to do that. If not, it’s a bug. Probably.

      Columns aren’t objects. They’re pieces of an object, namely tables.

      So you want to have the results collapsed by default? Not a bad idea…

      1. I can see the filter now.
        The formatting style does not allow me for example to say all keywords in lowercase (just uppercase) or have commas separating fields at the beginning not the end.

        Still not see a reason to decouple columns from Objects/Table. Let’s agree to disagree on this.

        Type Search definitely does not work on the Schema Browser.

        Collapsed yes.
        Also on that screen right now if you click on an empty space or a result. The result gets highlighted and quickly jumps to the Worksheet or whatever that screen is called (even if Open Object on single Click is unchecked).
        I might click by accident or might want to study the specific result without jumping. Perhaps two clicks here? or use shift F4 to go to that other screen?

        And just now I discovered that the search on the Preference screen does not work well or not as I expect.
        Open it and type “Click” or “click”. I was expecting to se the Open Object on single Click option. It does not. If I type Open it shows it.

        Thanks for the quick response!

        1. thatjeffsmith Post
          Author

          The formatting style does not allow me for example to say all keywords in lowercase (just uppercase) or have commas separating fields at the beginning not the end.

          In fact…it does.

          1. thatjeffsmith Post
            Author
          2. Ok. 4.2 does it.
            However, last_name should left align with job_id, not the comma if my type declarations are set to be aligned.

            Search on the Schema Browser works now.

            The search in preferences as well as clicking on the empty space of the find results still an issue here.

            Do you keep a page for suggestions?
            Thanks,

  3. Just found your posts, thank you. I have been using toad and trying to move away from it. One thing I miss is the default Schema Browser in toad.

    Is there a way to always start schema browser after a connection without always having to right click and select?

    1. This would be a nice add.

      Another nice add on to this would be the ability to add a “favorites” section option to the top of the schema drop-down where you can put the 3-4 ones you go to most often.

  4. Hi Jeff,
    when I am opening a package to edit
    there is no owner of the object in the .

    create or replace PACKAGE MY_PKG …

    could you advice how I can have it opened like this :

    create or replace PACKAGE OWNER.MY_PKG …

    1. thatjeffsmith Post
      Author

      we don’t support that, you can export it to include the schemaDOT notation if you want it though

      when you open the plsql, we know who it belongs to and take care of that when you go to compile or execute it

      1. Hi Jeff, thanks for the quick reply.
        Usually developers are compiling package on dev databases but on UAT , PROD the objects are deployed by scripts and the scripts can be run by different account.
        So if we are using SQL developer and opening a pkg to edit then when we are saving it as a file we need every time to remember to add schema owner.

        By the way in PL/SQL Developer , Schema owner exists if we open a package.

        1. thatjeffsmith Post
          Author
        2. thatjeffsmith Post
          Author

          >>So if we are using SQL developer and opening a pkg to edit then when we are saving it as a file we need every time to remember to add schema owner
          Right, don’t do that.

          You can right-click on it in the tree and say quick DDL to file or worksheet, or use the Export feature under the Tools menu. You have lots of options on how the DDL will be generated, including whether you want the SCHEMA notation included.

    1. thatjeffsmith Post
      Author
  5. Hi Jeff.
    First of all, thanks for your work on SQL Dev, especially new 4.1 version which still improved in the good direction.

    .. now my question 🙂 , related to keyboard so I ask it here.

    I got a lot of connections on my SQL Developer cause we work for different clients and different databases.
    That makes a long dropdown list of connections (in SQL Window or the “search object” window) and sadly keyboard is not really useful in these dropdown lists.
    In the SQL Window, I can type one letter and it takes me to the first connection starting with that letter while in the “search object” window, I cannot type at all to get me to the good connection.
    I’d like to know if you can improve that so that if works as described in this article for the tree component. I guess that maybe you are limited by the swing components or maybe you can do something about it.
    I have many connections starting with the same letter, that’s why it could be really helpful for me. I open SQL Dev with several SQL Windows already opened so that I have to chose the right connection several times a day and I don’t like using the mouse too much 🙂

    Do you think it could be possible to improve that point ?

    Thanks.
    Rémi

  6. now that new version is out.. is that still impossible to make this Schema Browser thing permanent? :]

    1. thatjeffsmith Post
      Author
  7. Nobody watches this sort of thing and it’s only when the devs grab pitchfork and storm the castle that it gets onto the to do list… Its there now though!

    1. thatjeffsmith Post
      Author
  8. Is there any way to tell SQL Developer to open the Schema Browser automatically when a new database connection is made? Also, is there any way to filter the schemas in the drop down so I only see a few that I work with regularly? The default filtering by contains, starts-with and ends-with don’t work for me as the schema names are too varied.

    Thanks, Abe

    1. thatjeffsmith Post
      Author

      Abe,

      No and no. However for the first request, that’s on my to-do list for our next version. I had a really good brainstorming session with the developer responsible for this part of the tool over a Giants baseball game, and after a few beers, we had some good ideas 🙂

      More powerful filtering would be nice, but not many users are clamoring for it at the moment.

      1. Thanks Jeff. Another question, I see that I can right click on a connection and “Reconnect” if I’ve already connected. However it seems my unshared worksheets do not reconnect. I can see how this make some sense, however I would prefer not to have to generate new unshared worksheets and copy/paste code. Is there anyway to “Reconnect” an unshared worksheet?

        FYI, I’m investigating SQL Developer as an alternative to some paid software. In the day I’ve been reading on you site, it has already been invaluable in getting setup and familiar with the software. Thanks!

        Abe

        1. thatjeffsmith Post
          Author

          The unshared worksheet connections are ‘hidden,’ although we DO know about them, and I can’t see a good reason not to have them included in the reconnect logic. I’ll see what I can see.

      2. *adding to the clamour* A more complex filter on the schema browser would be boss. Perhaps change the schema and table dropdowns so that they change their index/focus with keyboard shortcuts, but dont change to the selected schema until the user presses enter, a schema/table is clicked on, or the dropdown loses focus.

    1. thatjeffsmith Post
      Author
  9. Thanks for the blog post Jeff. Found it very useful and informative.
    Strangely enough that in my demonstration environment (google “oracle developer days 01” + 3.2 #SQLDev) drop down shows different results (4 tables in HR schema) than navigator interface (7 tables). I don’t think I set any additional filters or something. As an example drop down list doesn’t shows “HR”.”EMPLOYEES” table.

    I fonder if those is my crazy fingers’ fault or something else 🙂

    Yury

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *