Top 10 Tips & Tricks for Oracle SQL Developer

thatjeffsmith SQL Developer 59 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 59

  1. i have application form which captures record in two ways
    1. autosave
    2. submitting form

    my table has some required fields… for submitting application form is working fine but at the time of autosave i am unable to save required field

    which is best way to create table …
    two tables .. one table with required field and another table without required field ?

    thank you

    1. thatjeffsmith Post
      Author
  2. Hi Jeff,

    What is the shortcut key to achieve /* …. */ text comment using SQL Developer?

    Thanks in advance.

    Regards,
    Mani

    1. thatjeffsmith Post
      Author

      I’m not sure there is one. There’s one for doing — commenting though. I’m on vacation this week, but you can go through the keyboard shortcuts in the preferences to check for yourself, there’s even a Search dialog. Just type in ‘toggle’ or ‘comment’ and you’ll see what’s available.

  3. Hi….
    please give some details about real time projects or give some projects links related to plsql (industry standards).

    1. thatjeffsmith Post
      Author
  4. I have large code but when i copy paste it shows me in 1 line in
    Tool: PL/SQL developer
    Please Guide Stuck with it
    How to format it properly
    Example:
    –My code
    Select * from Employee131;
    –What I want
    Select *
    From Employee131;

  5. I have large code but when i copy paste it shows me in 1 line in
    Tool: PL/SQL developer
    Please Guide Stuck with it
    How to format it properly
    Example:
    –My code
    Select * from Employee;
    –What I want
    Select *
    From Employee;

  6. Hi Jeff,

    Is there a way that I can execute sqldeveloper with an EZConnect parameter that would enable me to connect to to a specified database when starting up SQL Developer.

    Some Context:
    I’m developing a tool to enable our team to connect to any infrastructure that we support from a central repository. The intention here is for me to define the command line that would enable SQL Developer to be started from another application and have it connect to the specified database. (not looking to run batch processes)

    1. thatjeffsmith Post
      Author
  7. Is there a way to automatically open certain saved worksheets (.sql files) when I start SQL Developer?

    I’m on version 4.0.3.16, Build MAIN-16.84 & Windows 8.1.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  8. I am looking for a way to unlock the table in Oracle SQL Developer so I can edit the query values in the grid. It’s a great feature I’ve used elsewhere, but can’t seem to get it here. Help!

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  9. Hi Jeff,
    Just a simple question
    When we create a table in pl/sql the result displayed is always table created.
    But is there any way to display the table name while creating a table

    1. thatjeffsmith Post
      Author
  10. Hi Jeff,

    Please tell me how can I create an alias for a keyword in SQL developer say SELECT as SEL and when I will run by typing SEL * FROM table name, it should work without throwing any error

    1. thatjeffsmith Post
      Author

      you can’t

      you can create a template called SEL that once typed and activated would turn into your SELECT * FROM… text.

      But you can’t run SEL and have SQLDev translated that to SELECT for you.

  11. Hello Sir,

    Could you please tell me how to bring result of multiple select queries in a single column. Right now sql developer brings result in a single row.

    Thanks
    Anil

    1. thatjeffsmith Post
      Author
  12. Jeff,

    I have SQL Developer 2, and never upgraded to 3 or 4 because in version 3, when browsing a table, and you filter a column, you have to remove that filter first, then the table refreshes, then you can put in another filter. In version 2, you could filter a column, then if you wanted to put in a different filter on the same column you did NOT have to remove the filter, but could simply overwrite the current filter, hit enter, and see your new filter enumerate.

    Please tell me there is a way to configure this in version 4. I do not understand why this was changed after version 2. It does nothing but take longer and actually cause more chatter on the DB.

    1. thatjeffsmith Post
      Author

      Still works that way.

      If you’re on a slow database, don’t use that feature. Use the Free-form filter dialog above, you can write your own query predicates there to be whatever you’d like.

      1. Our database is fairly quick. I like the filter by column feature because when doing a quick lookup, you minimize the amount to type by just typing into the filter. With the newer versions, you have to remove the filter first before you can type a new one. I take it you can’t change version 4 to act like version 2 did?

        1. thatjeffsmith Post
          Author
          1. Thanks for the very quick replies. I have one last question. Reading forums, I can see that some people are not happy with that particular design decision and feel it was a step backwards (i must agree as it’s an extra step in what seems for no reason). Is it possible to request a feature in newer version, there be an option to toggle overwrite of filter (like in sql dev 2) vs needing to remove the filter before applying a new one?

          2. thatjeffsmith Post
            Author
  13. I just installed the sql developer, have no idea about database connections or anything. I just want to run simple pl/sql queries. So it’s just a tool for me.
    how can i do that without all that database connections complicated stuff i don’t understand?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  14. Thanks for blog, it really helps a lot.
    one query is that when any connection is connected and idle for some time, then it takes much time to come back.
    Also it take time to disconnect conneciton / close SQL Developer.
    Any solution to this..
    thanks a lot once again.

  15. Hi Jeff,
    Great tips here! I’ve been used to PL/SQL Dev for good time and now I’m juggling between PL/SQL and Oracle SQL Dev. One thing I sorely miss in SQL Dev is the auto-replace plugin available in PL/SQL Dev.
    I started thinking of creating one myself and found this blog so want to check if such an extension/feature is already available. It is not the shortcut or auto-complete feature which I’m aware of.
    So basically when I simply type ‘sf’ followed by a space it gets replaced with ‘SELECT * FROM ‘ and I can create a whole text file with such short forms of longer SQLs.

    Cheers.

    1. thatjeffsmith Post
      Author
  16. Jeff,
    Thanks for the tips. I am new to Oracle SQL Developer, and I noticed that when I reverse-engineered a SQL Server database (2008), the newly-created model did not recognize that some of my primary keys were auto increment. When I perused the model, I noticed that I could set that in the IDE, but I would prefer that the tool recognize auto increments when I reverse-engineer. Is this a known bug, and is there a workaround?
    Thanks,
    Tracy

    1. thatjeffsmith Post
      Author
      1. Looks like it. Not a biggie, since from now on the data model will be the authoritative source for the DDL instead of the database. I had switched from Visio to SDDM, and subsequently pulled in the info from SQL Server.

        Have you seen this behavior, or is it possible you could give it a try and reproduce? If you do decide to do a service pack to address this particular bug, please let me know and I’ll re-download.

        Again, thanks for what appears to be a fully-functional end-to-end data modeling tool.

      2. Jeff,
        Here’s a simple question, but I can’t seem to figure out how to change the target database for the model, once it’s been created. I just want to point it at a database other than the one that I used to re-engineer, so that I can test the forward-engineering. That’s gotta be pretty simple, right?

        Thanks,
        Tracy

        1. thatjeffsmith Post
          Author
  17. how to disable auto populating table name as soon as entering schema name along with dot. This is causing more time to fetch. I hope this option will be there in pl/sql beautifier. Can you help me to disable the same.

    1. thatjeffsmith Post
      Author
  18. I’m running SqlDev 3.2.10.09 and the table drag from Navigation panel to worksheet doesn’t work (nothing happens). I can drag them from Nav panel to Query Builder, fuss with them there and then go back to the Worksheet to see the sql SqlDev has generated.

    I can’t find the “table alias” toggle/mechanism either. It’s using the full table name for all the join sql.

    Any thoughts?

    Great tip for the date format (NLS in preferences)…I can’t believe I’ve used SqlDev for years and didn’t know that!

    1. thatjeffsmith Post
      Author

      Do you have the Migrations extension disabled? That’s required for the DnD to work – a bug that we’ve fixed for the next version.

      Aliases are controlled in Tools > Preferences > Code Editor > Completion Insight…generate table/column aliases automatically or something very close to that.

      1. Thanks, that fixed it (I followed tip #3, before I did tip #5). That’s pretty cool! I’ve been writing them out of my head for years….Ha, ha, ha….

        Anyway to save the alias data so SqlDev will remember to use it everytime?

  19. Thanks Jeff. One more question if I may? I am also used to a ‘Copy Results with Titles’. Is there anything like that in this tool?
    Otherwise, your blog has been so helpful that I think my learning curve with this new tool will be minimal! I really appreciate it!
    Michelle

    1. thatjeffsmith Post
      Author

      When you say ‘copy’ do you mean a straight up copy and paste from the grid? And when you say ‘titles,’ do you mean column headers?

      If so, that’s another trick 🙂

      Instead of Ctrl+C to do a copy, use Shift+Ctrl+C – that will copy the data to the clipboard with the grid column headers for your query or table/view contents.

  20. Great tips!!!!! Being forced to switch to this from Golden so appreciate the quick start. One question… In Golden I can run a statement in edit mode – so I can edit the data in the results grid. Can I do this in sqldeveloper? That is the only thing I have not found out how to do.
    Thanks
    Michelle

    1. thatjeffsmith Post
      Author

      No, we don’t support that workflow. You’d have to find that row in the table or view it came from…but if you include the ROWID in your query, you could use that as a filter to make it ‘easy’ to get to.

    2. We are in the same boat here…giving up Golden for SQL Developer. The edit button is now the only remaining feature I really miss from Golden. It made edits so quick. Other than that, SQL Developer has been awesome.

  21. Jeff, I just wanted to say a big thank you for your fantastic blog posts. I hate SQLdeveloper when I used it in the last project. I thought that it was the clumsiest and most developer unfriendly tool I have used. This time, when I am starting on a new project, I thought that I will learn a few things about the tool and landed on your blog and ended up spending around 2 hours going from post to post and learning truck loads of tricks – now my respect for SQLdeveloper has gone way up. What a fantastic productive IDE it is. I did not realize its power. Thank you so much again.

    1. thatjeffsmith Post
      Author

Leave a Reply

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