Being a short week due to the holiday, and with everyone enjoying their Summer vacations (apologies Southern Hemispherians), I reckoned it was a great time to do one of those lazy recap-Top 10-Reader’s Digest type posts.

I’ve been sharing 1-3 tips or ‘tricks’ a week since I started blogging about SQL Developer, and I have more than enough content to write a book. But since I’m lazy, I’m just going to compile a list of my favorite ‘must know’ tips instead. I always have to leave out a few tips when I do my presentations, so now I can refer back to this list to make sure I’m not forgetting anything.

So without further ado…

1. Configure Your Preferences

Yes, there are a LOT of options. But you don’t need to worry about all of them just yet. I do recommend you take a quick look at these ones in particular. Whether you’re new to the tool or have been using it for 5 years, don’t overlook these settings!

2. Disable Extensions You Aren’t Using

If you’re not using Data Miner, or if you’re not working on a Migration – disable those extensions! SQL Developer will run leaner & meaner, plus the user interface will be a bit more simplified making the tool easier to navigate as well.

3. SQL Recall via Keyboard

Access your history via the keyboard!

Cycle through your recent SQL statements just using these magic key strokes! Ctrl+Up or Ctrl+Down.

4. Format Your Query Output Directly to CSV, XML, HTML, etc

Have the query results pre-formatted in the format of your choice!

Too lazy to run the Export wizard for your query result sets? Just add the SQL Developer output hints to your statement and have the output auto-magically formatted to the style of your choice!

5. Drag & Drop Multiple Tables to the Worksheet

SQL Developer will auto-join the related objects. You can then toggle over to the Query Builder to toggle off the columns you don’t want to query. I guarantee this tip will save you time if you’re joining 3 or more tables!

6. Drag & Drop Multiple Tables to a Relational Model

A pretty picture is worth a few dozen DDL scripts?

SQL Developer does data modeling! If you ctrl-drag a table to a model, it will take that table and any related tables and reverse engineer them to a relational model! You can then print it out or export it to HTML, PDF, etc.

7. View Your PL/SQL Execution Output Automatically

Function returns a refcursor? Procedure had 3 out parameters? When you run these programs via the Procedure Editor, we automatically capture the output and place them into one or more data grids for you to browse.

8. Disable Automatic Code Insight and Use It On-Demand

Code Editor – Completion Insight – Disable Completion Auto-Popup (Keyword being Auto)

Some folks really don’t like it when their IDEs or word-processors try to do ‘too much’ for them. Thankfully SQL Developer allows you to either increase the delay before it attempts to auto-complete your text OR to disable the automatic bit. Instead, you can invoke it on-demand.

9. Interactive Debugging – Change Your Variable Values as You Step Through Your PLSQL

Watches aren’t just for watching. You can actually interact with your programs and ‘see what happens’ when X = 256 instead of 1.

10. Ditch the Tree View for the Schema Browser

There’s nothing wrong with the Connection tree for browsing your database objects. But some folks just can’t seem to get comfortable with it. So, we built them a Schema Browser that uses a drop down control instead for changing up your schema and object types.

Already Know This Stuff, Want More?

Just check out my SQL Developer resource page, it’s one of the main links on the top of this page. Or if you can’t find something, just drop me a note in the form of a comment on this page and I’ll do my best to find it or write it for you.

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.

66 Comments

  1. Great tips! I’ll make use of the output format hints for sure!

    When selecting multiple queries and hitting F9 (or other equivalent methods to output into query grids) is there an output hint that will set the title of the result tab?

  2. Hi Jeff,
    I have a table with over 12,000 rows, it’s just one column. I need to use those values to query data using a query where I usually just use the format such as this … where ROWID in (12345,45678)
    How can I have the query look at the RowIDs that are in the table that I uploaded, instead of the ()s?
    Thanks.
    Liz

    • It’s very easy, just put a select statement in where you would have the list of values, so something like

      SELECT * FROM employees
      WHERE employee_id NOT IN (SELECT employee_id FROM employee_ids);

      the thing is the select in your IN, but ONLY bring back values that would work for your condition, in this case needs to be just a list of numbers…and the nice thing there is no limit of values, unlike if you were to type them out, you couldn’t have more than 1,000

  3. Jeff, Wanted to check if the SQL Developer has auto save sql worksheet option as of the auto save option in MS Word.
    thanks in advance.

Write A Comment