Top 10 Things You Might Be Overlooking in Oracle SQL Developer

thatjeffsmith SQL Developer 19 Comments

Tell Others About This Story:
The engineers don't want to work with you, mostly.

The engineers don’t want to work with you, mostly.

You do your job. You take the requests from the customers, and give them to the engineers.

Wait, that’s me.

Your job is probably something more like, you write SQL and PL/SQL, you build databases, you create reports, forms, and applications, you keep all of this running, you make it work, and you make it ‘fast enough.’

For much of your work, you live in SQL Developer. You are probably doing these types of things:

  • writings queries and running them
  • saving data to Excel
  • browsing objects and their data
  • getting SQL scripts generated
  • looking at database stuff to figure out what’s ‘wrong’

What I want to talk about today are the features that you’re probably not aware of or are using today. And these are features that could make the things you do DO, much easier and faster.

So without further ado (my all-time least favorite non-sequitur):

1. Reports

Not the reports we give you, but yes, those are really nice. I’m talking about the ability to write your own reports, and then re-use them over and over and over again.

Pie chart master with 3 children reports

Pie chart master with 3 children reports

Learn more about reports.

2. Search

It’s technically called ‘Find DB Object’ – but it’s a search mechanism for things in your database and database code. We have a button, a menu item, and keyboard hot key to launch and run searches.

Don’t go scrolling through the tree, just have the tool tell you where your stuff is, and then let it take you there automatically.

Search, browse, click to open.

Search, browse, click to open.

3. The Debugger

Yes, DBMS_OUTPUT is easy, it works, and everyone does it. But seeing what your PL/SQL code can be so much easier. And it’s free. Always has been. You’re in an IDE, why not try one of those fancy IDE-things?

Changing the value of a variable is easy with the debugger

Changing the value of a variable is easy with the debugger

Learn more about the debugger (3 minute YouTube Video).

4. The Cart

It’s not your fault that you’re not using the Cart feature. The name gives you maybe a hint at what it does (think shopping cart and e-commerce sites), but it’s hard to sum up what all this feature provides.

In a nut-shell, imagine a process where you could drag and drop one or more database objects, and then have ‘things’ happen to those objects. You could generate deployment scripts, use them in an compare against a database, or even upload them to our Oracle Cloud Database Schema Service.

For most of you, generating the deployment scripts is going to be the #1 use-case scenario.

I can simply drag database objects off the tree and build a 'shopping list'

I can simply drag database objects off the tree and build a ‘shopping list’

Learn more about the Cart.

5. SQL Code Templates

If you like snippets, you’ll LOVE this feature.

Code your code into SQL Developer.

Code your code into SQL Developer.

Learn more about templates.

6. File History

I don’t talk about this feature enough. If you work with files, then you really, really should be aware of the ability to see how your code is changing over time – without any need to setup CVS, Subversion, Git, or whatever.

SQL Developer can do local file tracking, but you probably should be using Source Control too!

SQL Developer can do local file tracking, but you probably should be using Source Control too!

7. Real Time SQL Monitoring

So IF you have licensed the Tuning Pack with your database, then you really should be using this feature. It’s available under the Tools – Monitor SQL menu.

Watch each step of the plan execute!

Watch each step of the plan execute!

Learn more about Real Time SQL Monitoring.

8. The Data Modeler

This is a BIG ONE. And there’s two things going on here. Some of you aren’t using it because you didn’t know SQL Developer included a full featured design tool for Oracle Database. But MOST of you wouldn’t be using it even if you were aware of it. Because most folks just start creating tables and writing code – and the model is an afterthought.

But, if you WANT to do design work, we can help you.

Oracle SQL Developer Data Modeler running INSIDE of SQL Developer. Notice how the Modeler menu items fold under the file menu?

Oracle SQL Developer Data Modeler running INSIDE of SQL Developer. Notice how the Modeler menu items fold under the file menu?

If you want to learn more about the Modeler, check out my resource page – it’s one of the main links at the top of this page.

9. The Preferences

Don’t laugh. Most folks never open the preferences and change anything. As much time as you spend in the tool, if you spend 5 minutes in there, it will have a profound impact on your productivity.

People are always asking me how to get the ‘alternating color thingy going in your data grids…’

Grid option for alternating backgrounds

Grid option for alternating backgrounds

Learn more about the preferences.

10. Potpourri, and other things YOU might enjoy

I could list a bunch of other things for this spot, like the DB Doc feature, or Unit Testing. But I’ll end on a more open-ended note, and just invite you to share your favorite feature that you wish your co-workers would take advantage of.

Tell Others About This Story:

Comments 19

  1. Can we run a sql statement in mode like nohup in the sql developer

    i am using create table abcd as select * from table3 ; this will take 15 min to execute till then my hands are tied .
    I am not allowed to do any thing in that session . We are opening other new sqlDeveloper for this kind of tasks .

    1. thatjeffsmith Post
      Author
  2. Hi,

    Loved the sql developer 3.2.20.09 experience. I recently upgraded to the Version 4.1.1.19. Now a few basic thinks like table filtering from navigation filter does not work , the error msg is “An error was encountered performing the requested operation:
    ORA-01460: unimplemented or unreasonable conversion requested
    01460. 00000 – “unimplemented or unreasonable conversion requested”
    *Cause:
    *Action:
    Vendor code 1460″ for all the objects
    I cannot open an object to view its attributes like columns , data ,constraint etc using Shift+F4 (it gives the error “No Object Found: tablename”) .
    The connections used for both the versions are same but it does not work for the Version 4.1.1.19.
    Can you please help figure out the issue.

    Thanks
    Renu

    1. thatjeffsmith Post
      Author
      1. hi,
        Sorry for the late reply,

        The oracle version is
        Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
        PL/SQL Release 9.2.0.8.0 – Production

        1. thatjeffsmith Post
          Author

          New version of SQL Developer uses a 12c JDBC driver. That driver is asking for things your old 9iR2 database doesn’t understand.

          You can use the older version of SQL Developer for your older databases, OR you can try using a 11gR2 Instant client in SQL Dev. For the latter, you’ll be making a THICK connection – that bypasses the jdbc driver and uses the client to connect.

  3. I’m a multi-window sort of guy and so I love being able to run multiple instance of SQL Developer. I’m also a Mac guy in this new office and so that gets a little hard to do. The command line (or Terminal if you are not a unix-weenie) to the rescue!

    In my .profile I’ve set up this alias
    alias sql=’nohup open -n /Applications/SQLDeveloper.app > /dev/null 2>&1 &’

    And now whenever I want a new instance I just type in “sql” and poof, a new window.

    I find window helpful when I’m working on MVC web stuff. The view code lives in one DB and the controller and model live on another. Web services make the hop over the internal firewall. Just a Command+Tab flip between the two sets of windows is easier to do that swapping between two sets of document groups (which I also use lots)

    And for those days when things are not going quite right and you need to run in debug mode…
    sql_log=/tmp/sqldev.`date “+%Y%m%d%H%M%S”`.log
    alias sqld=”nohup open -n /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper >> ${sql_log} 2>&1 &”

    This all works when using SQL Developer version 4.1.0.19 and OSX version 10.10.4. YMMV on other versions.

    1. thatjeffsmith Post
      Author
      1. Another simple way to do this on Mac is to modify the sqldeveloper.sh script in the SQLDeveloper.app/Contents/MacOS folder

        bash ./sqldeveloper >> /dev/null &

        You can simply launch the application.

  4. I love the new 4.1.1 update.
    Version 4.1.1.19.59, Updated June 29, 2015
    Is the fastest version I’ve used since 3.2

    I don’t what you fixed, but one of those 600 bugs must have been slowing things down.

      1. thatjeffsmith Post
        Author

        probably looking at about 1200 bug fixes since 3.2 came out till now, but also don’t overlook the improvements in Java since Java 6 and what we’re running now, Java 8.

        1. I’ve been keeping up to date with both SQL Developer and Java, but this specific update is just speedy compared to even the one just previous.

          Keep up the good work.

    1. thatjeffsmith Post
      Author
  5. I have been trying the SQL Developer reports for a couple of months and it really is helpful. I categorize them by functional area and common areas that I am responsible for. I have placed bind variables on most of them to make them more useful.

    Thanks!

    1. thatjeffsmith Post
      Author

      Love it!

      I’m starting to hear this more and more, which makes me happy. But most rooms I still get about an 85% miss rate on folks knowing about AND using the reports – so help me spread the word 🙂

    1. thatjeffsmith Post
      Author

      don’t think we’ll ever build that, but no matter – just open a report that does a select 1 from dual, with a refresh – that will keep your main connection alive assuming they are dying based on inactivity

Leave a Reply

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