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.
66 Comments
amazing tuts thanks sir
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
Sorry, I don’t know what you mean by ‘autosave’, and why do you have 2 tables?
Hi Jeff,
What is the shortcut key to achieve /* …. */ text comment using SQL Developer?
Thanks in advance.
Regards,
Mani
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.
ctrl+F7
ctrl+/
Hi….
please give some details about real time projects or give some projects links related to plsql (industry standards).
Sorry Ramesh, not sure what you’re looking for. Can you be more specific?
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;
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;
USE CTRL+F7
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)
Sorry, no.
You could do with with SQLcl though.
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.
If you have a file open when sqldev is closed, it will re-open the file at startup. Other than that, no.
hi Jeff, is there a way to delete multiple tables all at once?
Mostly
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!
there are two ways to change a query’s results:
I wrote this post for you 🙂
One more query , any way to insert or update a table while fetching data from an excel sheet
Not sure I understand, today you can do INSERTs to a table based on the contents of an excel sheet, but not UPDATEs
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
I don’t think so, even SQL*Plus doesn’t do this.
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
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.
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
So you want to pivot a row to a column? You can do this with SQL.
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.
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.
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?
Not for one user at least…we do have about 80% of our users using version 3.1 or higher of SQLDev
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?
Most definitely! You can do that here or open an SR with My Oracle Support.
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?
also i’m using mac os x.
Just me, my laptop and my sql queries.
i have no other information.
I wrote this post, JUST for you.
Hi Jeff,
Great tips …..thank a ton 🙂 fantastic blog posts ……CSV tip is good one …..
Thanks Raja!
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.
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.
I’ll say ‘yes’, but give this a read and let me know what you think.
Auto-replace in SQL Developer
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
Didn’t take them as identity columns then?
It SOUNDS like a bug…
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.
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
We made that a new feature..in 3.3 or 4.0 I forget…
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.
Tools > Preferences > Code Editor > Completion Insight > Enable Completion Auto-Popup in SQL Worksheet. Turn that off, then use ctrl+spacebar when you want the help.
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!
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.
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?
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
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.
Yes and yes,
Thanks!!!
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
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.
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.
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.
Wow, thanks Siraj for sharing your story here. This is why I blog!