Updated June 2015, current for version 4.1
There are too many options in our every day lives. We love asking for more options, but I often find myself being overwhelmed by the number of questions I have to answer before I can accomplish very mundane tasks. Do you want your latte ‘skinny’ or your cappuccino ‘wet?’ Waffle House customers are probably familiar with ‘capped, covered, smothered, & covered’ routine. Do you want a receipt with that?
So what’s the sweet spot between the Spartan-like Notepad, where we have no options, and the current lineup of Republican Presidential candidates, where we have lots of options that don’t do anything? — OK, that was a political joke, and I should refrain from doing these as I am not a comedian.
When it comes to software, you want something that pretty much works out-of-the-box. Just starting it up and using it shouldn’t require several hours of digging through options or preferences to get it to do what you want it to do. I think we’re pretty close to this with SQL Developer. I can’t think of any ‘deal breaker’ option that would put a user down the wrong road right out of the gate. Here’s an example of something I would see as ‘bad’:
Preferences – Database – Advanced – Autocommit: if this were set to ‘True’ out of the box, I would most likely freak out and remove the product immediately. But, some crazy folks out there might think this is a good idea and demand they have this level of control. These people are also probably from an evil parallel dimension where this is necessary to prevent an obnoxious level of object locking, but that’s a different story.
So most things are good out of the box. But.
What options SHOULD you be aware of?
Here are ten that I think deserve your attention. If you need help navigating to any of these preferences, simply start typing their names into the search box. DO NOT HIT ENTER, this will close the preferences dialog.
1. Code Editor – Fonts
You’re going to be typing a LOT. Be sure to set a font that’s pleasing to the eye. I’m a fan of fixed width fonts that support Unicode. There are thousands of font out there to choose from. One to check out are the Droid fonts from Google.
2. Database – Licensing
As much as we would like for everyone to be running Enterprise edition with ALL the database pack goodies, many people are running lower licensed editions of the database. You can disable the Tuning and Diagnostic Packs in SQL Developer to avoid accidentally using and licensing these features in the database. I recommend disabling the packs at the database level to protect yourself, but if that’s not possible then at least do it in SQL Developer. The nice part is you can do so at the connection level rather than neutering SQL Developer completely.
3. Database – Worksheet – SQL History Limit
The number of statements you want saved is defaulted to 100. I think this is probably on the low side. Bump it up to 500 if you want a few days more worth of queries in your ‘backup.’
4. Database – Worksheet – Show Query Results in new tabs
Wouldn’t it be nice to keep around query results from different iterations of your query as you work through it? Enabling this feature will keep your query results open as you execute new queries. Mouse over the ‘Query Result’ labels to see the SQL statement used to populate that grid. Of course the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished.
5. Debugger – Start Debugging Option
Set this to ‘Step Over.’ This allows you to start and run a debug session sans breakpoints. If you leave it at the default, starting a debug session will appear to ‘not work.’ This is because SQL Developer will enter debug mode and run until it finds a breakpoint, breaking condition, or exception. If you have none, it will execute to the end of your program. The ‘Step Over’ option will tell SQL Developer to stop execution at line 1 auto-magically.
6. External Editors
Spend a few minutes and setup your default editors for your JPG, XLS, XML, and PDF files. Please. This will make working with BLOBs much more pleasant. A post all about that here…
7. Shortcut Keys
So here’s a trick: sort by ‘Category’ and scroll down to the ‘Other’ category. I’ve found some of the best keyboard shortcuts are ‘undocumented.’ You’ll find a lot of the fun ones here. My particular favorite is ‘SQL History: Replace with Next.’ Of course if you do not like any of these shortcuts, you can customize them to your liking. Just mind the conflicts!
8. Open Object on Single Click
Good for n00bs, probably annoying to experienced users.
9. Hiding Database Object Types from your Connection Trees
You probably don’t work with EVERYTHING in Oracle – so set your trees to show just what you need. Less scrolling, less searching. More happy.
10. Connection Script Startup
Don’t care to toggle on DBMS_OUTPUT? Want your script page results in groups of 25? Setup your connection startup script (LOGIN.SQL)
Is that it?
Hardly. I could do an entire hour on just configuring your application preferences. But this is a Top 10 list, so it should give you an idea of what you can control via preferences.
If you need a bonus, check out this post on setting up the automatic code insight behavior. Watch the movie at the bottom of the post for insight on the preferences that affect that feature.
Jeff, I just wanted to point out that font customization seems not available at all in newer versions of Windows 10. I’ve just installed a bunch of fonts i like to use but the drop down does not show them at all.
I’ve noticed that new installed fonts on Windows 10 go to %appdata%/Local/Microsoft/Windows 10/Fonts/ and seems like SQL Developer does not look at this folder for font customization.
Is there any way I can make this folder available for SQL Developer fonts?
Hmmm, this might help us figure out what’s breaking here for some users!
However, this feature is still working for me, and I’m on Windows 10 Pro 1903, OS Build 18362.1198.
What build are you on?
I’m on Windows 10 Pro 2004, OS Build 19041.546. I’m also a domain user without admin privileges. I’ve also tried to change the font with a user with admin privileges and still no luck.
The problem is somewhere between your domain admins and your OS…you need more privs/rights to work on your machine.
I’m new to SQL developer and have kind of a dumb question I haven’t found any answers to yet. This is regarding the editor/query builder.
Default editor behavior places your bottom line of code at the bottom of the editor window. For me, most of the time, this means the bottom of my physical display, as I work with the editor maximized.
Many editors allow for vertical scrolling to scroll so that your last line appears somewhere other than the bottom of your window. In default behavior this can be simulated by adding a bunch of carriage returns after your last line of code to position the bottom line somewhere in the middle of the window. Are you aware of any setting in SQL Developer to enable this kind of scrolling behavior? Thanks.
I think what you’re asking for is
Preferences > Code Editor > Use Jump Scrolling for Keyboard Navigation
As I scroll down using down arrow, when I get past last line, the editor will ‘jump’ the focus up to the middle of the page with the next bit of lines in the buffer/file.
Is there a short key or button to display a table content without writing SQL statement (e.g. SELECT …). I saw a demo session that a user were clicking the table name on the left and a new tab open (on the right) displaying sample records in the table. I thought it is niffy and convenient to profile the data in table without typing a SELECT *. Thank you in advance!
You can open the table using the connection tree or by using the ctrl-click trick.
Is there a way to display the user in the banner, top left corner? Right now it displays “Oracle SQL [email protected]”. I am on Linux. Would like see under which Linux user I am logged in.
click on a worksheet or table…you’ll see the connection name
Sorry, I wasn’t clear. I am looking for the OS user. At the top of the worksheet I see the connection name as I named it under Oracle Connections. However, I am looking for the OS user in the banner. Right now it shows only the Linux host name.
Why would you need SQL Developer to remind you of the OS user you are running the application as?
To answer your question though, sorry, there’s no way to do this…at least not w/o us writing some code to add it.
I frequently have multiple connections open between environments and couple users. I want to know where I am running SQL Developer and as who. Looks like there is already code returning hostname to banner. What I am looking for is similar to what I see in the Linux shell on every command line: [[email protected]] $
workaround: in a SQL Worksheet, run
You’ll see the USER Home dir listed, and THAT will tell you the OS user
Thank you for the tip. Wasn’t aware of it. Okay as a workaround, but I would still add a request as nice to have to add the OS user in the banner.
Sorry that you are someone who has to inject politics into everything.
854 posts over about 9 years and I think that might be the only political joke I’ve dared put out there…so can you please define, ‘everything?’
While reading the “4. Database – Worksheet – Show Query Results in new tabs” section, I read:
Quote: “[…] Of course the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished.”.
By “memory” do you reffer to “RAM” here? – The reason I ask is I did remember read in one of your entries that by leaving a “query results” it keeps the conenction open or some related in these lines that might makes bad to any DBA. Did the same applies to Console Outputs?
It CAN be both.
Server resources are tied up as long as you have a query running and/or results to be fetched to the grid. A DBA will notice this.
Client resource are tied up as long as you have those grids open. YOU will notice this.
Console…not so much, because we fetch all the results all at once for your queries — unless you build in a pause per page of results, but that’s as very special case.
Great article, but PLEASE omit the entire first paragraph. It adds nothing, and wastes time reading:
“There are too many options in our every day lives. We love asking for more options, but I often find myself being overwhelmed by the number of questions I have to answer before I can accomplish very mundane tasks. Do you want your latte ‘skinny’ or your cappuccino ‘wet?’ Waffle House customers are probably familiar with ‘capped, covered, smothered, & covered’ routine. Do you want a receipt with that?”
I’m guessing it took you longer to leave this comment than it took you to skim over that first paragraph?
That’s how I tended to write things 8 years ago, I felt it necessary to start a story vs jumping right in. But now with folks attention spans being what they are, I tend be more terse and less story telling. It’s not my style, but I’ve adapted.
Adapt to others rather than trying to make them adapt to you.
For example, if you’d pick up some simple speed reading techniques like clustering…. it wouldn’t even be an issue. Then you could just stop at “great article” and omit the entire rest of the message which adds nothing and wastes time reading.
I’m using ver 18.2 and cannot figure out how to prevent a couple of dozen recently-used files from opening automatically when I start SQL Developer (each in their own worksheet/tab). I have looked through every setting I can find and searched every way I can think of and haven’t found a solution.
I see the same issue, so annoying, wish there is a solution.
You must close each open file before exiting SQL-Developer or they will reopen automatically the next time you launch the app.
Personally I love that feature as I often open multiple files to reference old code and then only leave open the code I’m currently working before exiting the app. You must be disciplined about it or you will have a bunch of files will reopen if you simply close the app.
There is a preference under Database: Worksheet called “Close all worksheets on disconnect” So if you right-click on connections and disconnect all connection, then all worksheets will close and they will not be there when you relaunch. You can then use the recently open feature but again, you need to manually pair down the open files in order to leave only those handful of file s open at the last to have only those available in the “recently open” list.
I’m using tool_env_getvar in a package. when I compile the package I get the foll. error :
identifier ‘TOOL_ENV.GETVAR’ must be declared.
How to fix this?
The database doesn’t know what ‘TOOL_ENV.GETVAR’ is. Does it exist? Does your user have EXECUTE PRIVS for it?
I need your help. I’m using SQL Developer version 18.1 and I’m having issues with special characters. Specifically the Em Dash. (—).
When I try to do a: SELECT ‘—’ from dual, I get a Question Mark (?) as a result. Now I know this is related with the NLS_LANG configuration. Other IDEs (like PLSQL Developer) are fixing the issue when I add an environment variable in Windows 10, with this value:
NLS_LANG = ‘AMERICAN_AMERICA.WE8ISO8859P1’
But it is not working in SQL Developer.
I checked the nls_database_parameters and v$nls_parameters, and both have the value:
NLS_CHARACTERSET = ‘WE8ISO8859P1’
Any idea how to fix this? Appreciated.
Is there any way to remove or hide the ‘Oracle NoSQL Connections’ and ‘Database Schema Service Connections’ categories in the Connections panel? Yeah, they’re out of the way down at the bottom, but I have no prospects for using either one, so seeing them isn’t helpful.
Also, a suggestion: sometimes I want to organize connections by application, other times by environment (dev/test/prod). Folders are great, but I need to pick one scheme or the other. Any chance we’ll someday be able to put a connection in more than one folder, or have nested folders, or be able to switch among connection lists?
You just need to upgrade to 18.3 – nested folders, Check! Hiding unused connection types, Check!
Any chance we’ll get someday folders also in the drop down on the SQL Worksheet ?
And what about a configuration flag “Show open connections first” for the drop-down list ? If you have several SQL Worksheets, the order is (open connection, closed connections), but at time of SQL Worksheet creation. Subsequent connection openings don’t show up at the top. And newly created connections always appear at the bottom. This gives a feeling of random order and is especially annoying when you have 400+ connection definitions.
Yes, that’s on the list for 19.x
What you’re describing on the connection order is a bug. If I can replicate it, will try to get fix for 18.4
When I open a SQL file which is attached in an Outlook mail, it opens correctly in SQL Developer, but in read-only mode as the file is temporarily saved in C:\Users\Username\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\LM9N06BP with the read-only flag set.
How can I easily change the editor mode so that I can edit the file ? Menu ? Mouse click ? …
I could save the file at some other place, but that will need some extra cleanup when the work is done.
Thanks for any help.
I wonder if there’s an Outlook setting to not make those files READ ONLY, but I’m guessing it’s a SECURITY feature.
No way to do this from inside of SQL Developer that I know of.
I’d probably copy it out of the preview window (text to clipboard) and paste into a useable sql worksheet if you’re doing this a lot
Thanks for your answer.
In outlook, you can edit the message and then open the attachment. But that way you may change inadvertently the file in the message you received, which is something I would try to avoid.
It would be nice to have this ability directly in SQL Developer, like most text editors do. How do I submit a request for this ?
instead of emailing data around to folks to get changed and then whatever, seems like a better idea to build an APEX app so folks can edit/share the data live with just a web link
We’re not using it to develop code (these scripts are stored in subversion or git to keep track). It are requests to fix data or hot patches in schemas where developers have no direct access. These scripts are coming via mail or via our issue tracking system. In both cases the file opens read-only, but sometimes I need to change an ID, a value or add an alter session before running the script. That’s why I need to be able to modify the file. These small changes will never be kept.
you’ve just perfectly described a scenario that could be solved with an APEX app
Is there any way to right click on an object in the left hand connections pane and debug it without having to open the object itself – then do it through the pop-up debug window and select from all the funcs/procs in a package?
Its a feature in Allround Automations plsql tool that was amazingly handy and fast for investigating issues.
it’s one click to open, and one more click to debug…and then when you are debugging, we need to have the code editor available – so, no…sorry 🙁
thanks for this article.
I have an additionaly question.
Do you have an idea, how I can prevent SQL Developer to open the welcome page?
thanks and kind regards
Scroll to the bottom, uncheck the box in the left corner.
Is there a way to shrink the visual display of a table/object without having to constantly drag it to a smaller size? I have a table with about 30 fields. The table expands automatically (even though option is off), and runs off the page. I have to scroll down to find a field I want. I am hoping for a way that tables maybe stay at a certain length.
For the Query Builder?
Yes. When I drag tables into the query builder, they expand vertically. So I could have one table with 5 fields, and another with 30.
If the required fields to link are not close on visual, I have to manually try and shrink the table visually. In other words, is there a way to have to resemble MS Access in the sense that the tables don’t expand vertically automatically?
No, not that i know of. If we were to shrink the size of the table automatically though, you’d still have to scroll on the table to advance to the columns on the bottom. And aren’t most ID columns in a table near ‘the top?’
I’m not saying it’s not a good idea, it’s just not in the product today.
Would you please advice how to show the default startup page that is shown the first time the application started?
I removed the checkbox then, but need it again.
Help – Start Page
Thank you, but could not find it.
Found it, so many thanks.
Can someone advise what am I doing wrong.
I have following lines in login.sql file which is located on C:\app directory, I gave the complete path in “filename for connection startup script” but it doesnt work.
SET SERVEROUTPUT ON
Any help in this regard is appreciated
Try ‘show login’ – run F5
Does it show your file?
Hi Jeff, Thanks for quick response.
I tried show login as you suggested but it doesnt show my file rather displays the following message in the script output.
SP2-0158: UNKNOWN SHOW option “login”
Assuming show login is wrong option, I tried show user command, it gives my username in script output tab.
Sql developer version is 220.127.116.11 and build is 15.21.
Ok, version 4.0.2 is more than several years old. Can you upgrade?
Any idea how to import a pl/sql scheme so SQL Dev looks like TOAD?
How do you mean a ‘pl/sql’ scheme – you mean the code syntax colorization effects?
Yes the colors, but also the indentations, formatting, etc. was hoping there was a way to import a scheme because the SQL Dev options aren’t easy to work with.
Aren’t easy to work with – not sure what that means.
We don’t offer a ‘Toad mode’ 1)because that would be weird, and 2)we think it’s ugly and overly complicated.
With a little work you can pretty much make SQLDev look and feel any which way you’d like.
Jeff, In an Oracle SQL Developer request on 2011. A request was made so that you can use the keyboard to go from the SQL Worksheet to the Query Results tab.
Has this been actioned?
Alt+PgDn to go from editor to grid, Alt+PgUp to go from grid to editor.
And would you have the answer of changing tabs in the results grid?
Cancel the last request, it Alt+ the arrow keys.
However, is there a keyboard keystroke to close the grid result tab?
Don’t think so.
Ctrl-W, of course!
When Im working in plsql code for a long time, sql developer gets disconected from the database and when I try to recompile, the GUI gets freezed and nothing responds anymore. Sometimes when a little of lucky, disconnecting the VPN makes sql developer to respond again and I can continue my work, but many of the times I have to kill the process. I have seen this annoying behavior since version 3.x. Im using 18.104.22.168.260 now.
Does anyone know if this have been fixed or reported at least?
If it’s been awhile, i would right-click reconnect your connection and THEN to the compile.
Thanks for your response, thatjeffsmith. I do that, when I remember to do it otherwise I deal with the app hanging…
we’re left there waiting for the db to respond or for the jdbc driver to realize the connection isn’t avail anymore
You CAN try a thick connection if you have an Oracle Client installed, that might provide some relief.
i am creating package using SQL developer.
When I editing any thing in package. SQL developer start to check for error. because my package is very lengthy, it’s took time to re verify the whole package for any error.
It’s very annoying . can we disable this. so we can see the error only when we compile the package
you mean the parser checks for syntax issues? We don’t check for pl/sql errors until after you do a COMPILE
I just started using SQL Developer. I am opening a SQL script that was saved in another tool that I was using. It opens with these characters: ÿþ
and puts a space after each letter like:
s e l e c t * f r o m …..
How can I fix this?
Where did the file come from?
What’s the encoding?
What’s the file look like in Notepad?
We had written it in DBArtisan.
Default Encoding was ANSI.
Opens up in notepad with no proper alignment or indentation, but without space.
weird…it’s just a plain text file?
are all the files like that, or just this one? can you send it to me?
Ah.. Just realized that some of my colleagues had the default encoding ‘unicode’ and that’s what is causing ÿþ and spaces.
Thanks Jeff for pointing me to the right direction.
Hi, Im using oracle sql developer 4.1.2. I find the opening of multiple SQL query result window/tabs mildly annoying. I’m sure there are very useful cases for this feature, but my question is: Can we turn the multiple query result windows to just one (Toad style).
I tried Tools > Preferences > Database > Worksheet >uncheck “Show query results in new tabs”
But Still I see the same . please help me
if that preference is unchecked, that shouldn’t be happening. you might want to upgrade to v4.1.3
I have upgraded to V4.1.3. Still the same issue exists.
Did you start with a ‘clean’ install, or import your preferences from 4.1.2? I’m hoping it was a clean, no import?
I have started with import preferences from 4.1.2. Ok let me try the other way now.
I am having an issue with opening a New Connection in SQL Developer. I am not sure why but everytime I right click on Connection and then choose New Connection, nothing ever happens, no box appears. Is there some type of setting that could cause this?
do you have the same issue if you hit the Green Plus/Add button up on the toolbar to create a new connection?
Are you able to edit an existing connection’s properties?
Yes, the same issue when just trying to hit the Green Plus/Add button. I currently don’t have any existing connections. I am taking a class in PL/SQL programming right now and I am trying to create the connection. So, no existing ones yet. I contacted the schools DBA Helpdesk to see if they knew. They only suggested closing SQL developer and reopening it. Which I have tried a million times, so I am at a loss for words right now.
Something is ‘messed up’ with your install. Are you Windows or Linux/Mac?
I am using windows. However, SQL Developer is not downloaded to my Hard-drive. I am logging onto my schools online application desktop and then starting it through there. I don’t know if this changes anything though.
ugh…remote desktop…not my favorite.
ok, well it means this isn’t your problem, the team that manages that desktop environment needs to check that the install is valid. For example can the application user create a file in the AppData, Roaming Profiles directory? If not, they’ll need to tell SQLDev to write the application preferences files somewhere else.
We have a VirtualBox appliance you could run on your own machine – it has SQL Developer and a database all setup for you to use. You get to own your own environment.
In latest sql developer version 4.2 ; the script output is coming with a CF ; appending extra CF after every line .. For instance i ran three inserts> it is showing up as:
inserted 1 record.
inserted 1 record.
inserted 1 record.
How to make this extra CF go away ?
extra, how? the output looks exactly the same as SQL*Plus to me