This post was inspired by two simple events –

  1. watching a co-worker use SQL Developer over Zoom
  2. an innocent enough tweet from Franck

I’m a horrible ‘back seat driver’

It bothers me to watch people do stuff less efficiently than they could be. This feeling, or reaction was the driving force behind my “SQL Developer, You’re Doing it Wrong” video. You can watch it now of course, but it’s about 17 minutes long. This post will be over in just 2 more minutes 🙂

I ALSO wanted to get down to the lowest possible level of this concept. What are the very, very basics of the UI/UX in SQL Developer? End of day, Franck just pushed me to ‘get busy blogging, or get busy dying.’

1. To run a statement, just hit Ctrl+Enter

I was watching my co-worker, write her statement, then circle back, select the text, then use the ‘Execute’ button on the toolbar.

Absolutely NOTHING wrong with this.

And if you’re using statement delimiters, or if you only have a single statement in your worksheet, it’s completely unecessary.

If you don’t use statement delimiters – the semicolon – you will need to highlight your code first.

2. Avoid the ‘Clear’ button, just use Clear Screen

I use this all the time, and so does Franck!

As you’re testing a snippet of code, you’re going to be making changes and running it, over and over again. Now, sometimes you’ll want that running log. But most times, not so much.

I was using the mouse to execute the script, but F5 is faster.

3. Organize your connections

There’s really 2 tips here. The first is, give your connections GOOD names. The second is, organize them.

The easiest way to do is this is to add them to a folder, or even subfolder.

You can nest as many levels as you want, and you can do this on the DBA connection panel, too!

4. Quickly Clone a Connection

I’ve seen people do this so many times…you have a connection to one database. You want to add a second connection to the same database. They start from scratch!

No, it’s easier than that. Just edit an existing connection, give it a new name. We don’t ‘nuke’ the existing connection, we’l instead create a new one with the new name, and the original one remains.

You don’t have to Test your connection first, you can just ‘Save’ it, if you’re confident 🙂

5. Avoid the connection tree if you can

There’s nothing wrong with the Connection Tree. Tons of features are available there. It’s also the easiest way to explore the contents of your database.

But what if you don’t need to explore? What if you simply want the DDL for an object, or maybe a list of indexes or foreign keys?

I have two ways to get this information, faster than you can get it by clicking around in the tree.

Use the DDL command, and use the INFO command. We built them for SQLcl, but they’re also available in SQL Developer and SQL Developer Web!

Try INFO+ if you want the column stats vs comments. That was Tanel’s idea.

Maybe I should start a Twitch channel?

Instead of killing the bad guys on my PS4, you could watch me kill bad SQL or create REST Services.

Yes, I’m joking. But watching people build a website or write a program or navigate the BASH always captivates me. What are they doing, and how are they doing it so quickly???

If you have suggestions or have a tip you’d like to share – leave a comment!

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

9 Comments

  1. Piotr Kowalski Reply

    Yep,
    that’s just my opinion. Such option exists in “the frog”. Maybe I have been using it too long before I started using SQL Developer ;).
    Thanks.

    • thatjeffsmith

      I used to be on the ‘frog’ team, and I saw how many issues it caused for people not paying attention there. There’s a reason I do not want this behavior in SQL Developer.

  2. Piotr Kowalski Reply

    Hi,
    At the beginning I would like to thank you for your greate job (Your’s and the Team).
    Concerning launching the statements by ctrl+enter. It is of course greate option but for me it would be nicer if SQL Developer would NOT expect from user to write the semicolon at the end of each statement. It would be nice if the SQL Developer would discover the end of the statement, for example by the empty line before/after the statement, or even better, if it would be able to detect the end of the statement by context (new line before/after the statement, another select statement before/after the statement, something else that is not a SQL statement or comment…).

    Regards

    • thatjeffsmith

      That’s very dangerous

      Consider the following:

      update employees
      set salary = salary * 1.1

      where employee_id = 1234

      In your scenario, you would have us give everyone in your company a 10% raise. They would be happy, but you’d be in trouble. And I’m sure you wouldn’t blame the tool, but I would.

      Best not to ‘guess’ what you SQL you want to run, and be explicit. So either include the (;) or explicitly highlight what you want executed.

    • Piotr Kowalski

      Thanks Jeff for your reply.
      I agree with you that it would be dangerous but I have slight different approach:
      When I do an update I always try to be 100% sure what am I doing. So I rather highlight whole update statement or I just use semicolon in a case when the whole statement match in the window, so I can see this at once. As far as I’m concerned, 99% statements I use are selects. That’s why I think it would be good to have although such option to be able to use it consciously. As an example for something similar I would mention autocommit option. When you have it turned on, and you try to do some update only for testing purposes without commit (eg. to check how will some trigger work) you could do something really bad for your data.

    • thatjeffsmith

      If it were up to me, I’d nuke AUTOCOMMIT!

      I agree with you in concept, but not in practice. When we start guessing what you want to run, bad things will happen. So I’m on the safe side of the fence. I don’t think it’s too much to ask users to be specific about what they’re running, esp when it’s just a single extra keypress.

    • Just start using DBeaver. It let’s you choose, if you want an empty line to be a statement delimiter.

      And it has tons of other advantages.

      http://dbeaver.io

      And it’s free as in beer!

  3. Jens Gerhardt Reply

    Thanks a lot for sharing this!
    I didn´t know about info+, nice feature.

    Regarding the folders and Subfolders and the cloning of connections… When I create a new connection or want to clone a connection its very inconvenient first to create the connection and afterwards having to move it to the correct folder. Would it be possible to implement it that I´m able to select the folder directly while creating the connection?

    One more thing: If I search for an connection (directly typing the name) while the folders aren´t expanded I don`t find them. Would it be possible to search also in folders and subfolders that aren´t expanded and expand them with the search?

    Thanks a lot in advance

Write A Comment