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
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
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
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
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.
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?
No, but if you mouse over the tab, it’ll show you the sql behind the data in the grid
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?
It’s very easy, just put a select statement in where you would have the list of values, so something like
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
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.
There’s an auto-save on Exit, but not a background auto save for open files.