Update: we added the auto-replace option in a later version of SQL Developer, so if you don’t see it in your version, UPGRADE.
I get this question every few months, and I got it again last week in Denver –
How can we setup auto-replace in SQL Developer? If I accidentally type ‘form’ I want it to come out as ‘from’ for example.
I’m not a huge fan of the IDE ‘automatically’ doing things with my editor text. So this is not the default behavior, but you can set it up.
We can get you there with the SQL Editor Code Templates.
So type, then hit Ctrl+Spacebar to activate.
Or, check ‘Autoreplace’ in the dialog, and as soon as you hit the spacebar or carriage return, presto-bingo.
Or watch the movie:
28 Comments
Hey Jeff, I realize this is an older post, but I’m trying to bulk add auto replace entries. I currently use Squirrel SQL, which is not exactly “enterprise level” software, but still packed with features. In my autoreplace library, I have all the table names and schemas with an automatically defined correlation in addition to the usual ssf etc. I was able to find the xml file in which those were stored and add multiple entries at once, more than 75.
Can you point me to the location of that file in SQL Developer 18.4? I’ve searched for autoreplace.txt and auto_replace.dfn to no avail.
If anyone else stumbles across this post, the location is here:
%AppData%\Roaming\SQL Developer\CodeTemplate.xml
Is there a way to use a star (*) as part of a auto replace feature.
I have abbreviation “s*” for “select * from” and it does not work at all. If I use only letters than it works.
Is there a way to use * or other special characters.
I like to use star as I don’t have to be careful about words not being used otherwise.
Thank you.
Probably not – the parser is looking for ‘*’ to activate other things, namely this
I have what you have, but I call mine SSF
Hi Jeff,
I have a problem to get this working. I am using a german keeboard. Even the predifined code templates do not work propably. Any thoughts what could be the problem?
ctl+spacebar i snot working could you please suggest the setup required ..
No setup required.
What version of SQL Developer are you using?
What does your sql template look like?
Are you in a worksheet or a code (plsql) editor?
This is the app installed on remote machine, I think this is the issue I have to live with 🙂 Thank you a lot for explaining the reason. of my issue. Will read all your blogs further, they are really usefull.
Hello Jeff,
Thank you a lot for sharing all the informationyou gathered. I have a question for me ctr+space bar does not work, do you have any idea why? Might it be that this short cut is not set in preferences? If so which action has to be shortcuted?
Thanks in advance,
Dmitrii
it’s probably not working b/c the parser can’t ID what text you have at the cursor – the most basic test is something like the following in a completely blank worksheet:
select * from scott.
Does that bring up the list of tables from SCOTT like BONUS, DEPT, EMP?
Nope, nothing happens. Does this SCOTT a regular table which is present like a dummy information in all the DBs? When I’m typing my ‘auto replace word’ tooltip appears with the ‘to replace’ code, but nothing happens on hitting ctr+space, I can just double click it by mouse, but the thing is that it looks after this like ‘auto replace word”to replace’ . What I’m expecting is to see only ‘to replace’ part within worksheet.
Best regards,
Dmitrii
Sorry, I got off on the wrong track when you brought up ctrl+spacebar – I was thinking of the code insight, not the auto-replace. I found some bugs in that in an earlier release. What version of the tool are you using today?
It is 3.2.20.09 Build MAIN-09.87.
Yeah, I got that fixed in v4 – try and upgrade
Hi Jeff!
Is there any chance that some day we would be able to write code like this:
1) sf table_name (of course I expect autocomplete to suggest the table_name)
2) press Tab
3) IDE generates ‘SELECT ? FROM table_name’ with cursor placed at question mark to continue with query editing.
I’m so tired of having to type ‘SELECT * FROM table_name’ and then get back to ‘*’ to finish the query autocompleting column names.
Maybe. You should suggest that on the Exchange as a new feature.
I commend you on not using select * queries though – explicitly named columns is one of my favorite best practices. Have you noticed the hover/expand feature for ‘*’ in 4.0 though? If you pick up your mouse you can hover/click to explode the * to the fully qualified list of columns. Not what you want, but maybe just a bit less frustrating?
Hi ,
In PL-SQL Developer , we used to have a Auto Replace functionality which automatically import all the auto_replace.dfn (A definition file ) with multiple shortcuts at a go . Looks like that is not the case here and you need to type each one of them separately .
This isn’t PL/SQL Developer, so…
That sure would be a nice feature to have…
for example in my case I work in several environments and would be nice to have an easy way to export-import them.
anyway do you have to activate this somewhere?
because I do type ctrl+space after my auto-replace and nothing seems to happen.
maybe I have to set it up somewhere else before?
What version are you on Miguel? There was a bug that was causing this not to work for some strings that got fixed in 4.0.1 or 4.0.2…
yeah I was not so smart.
I kept reading the rest of the comments and found your answer about similar problem and yes I am also in 3.0 release.
Will have to try and ask my company to update our sql developer version…
but mainly I just commented on this reply because I was also expecting to find such a feature of export-import of the auto replaces.
maybe I can find it somewhere in the configuration folders… most likely not
you could just simply zip up your application data directory and move that around, then you’ll have your connections, SQL History, and everything else
ok it work in the 4.0.2 thanks.
by the way is there a way to change the trigger from ctrl+space to something else?
There sure is. Tools > Preferences > Shortcut Keys.
Code Editor – Completion Insight.
Pick your own.
I ran into the exact same issue as Brian. Following Jeff’s suggestion – I renamed the top-level directory and tried again. That sorted the issue.
This is fishy!
MK – this was on a first-time run after importing your settings/files from a 3.2 or earlier install?
I really enjoy your blog.
A quick issue here.
I’ve reinstalled the most recent SQLDeveloper today (4.01.14.48) and installed jdk-7u51-windows-x64.exe
When i start sqldeveloper.exe i get the following message..
“Warning – could not install some modules: Dynamic Module Config – No module providing the capability org.netbeans.Netbinox could be found.”
My sql developer 3.2 works just fine.
What could be the issue ?
thanks
Brian
I have guesses:
1) bad download
2) you extracted it into an existing directory (the word reinstall makes me nervous, ALWAYS extract to a fresh directory)
3) Do you have a org-netbeans-modules-netbinox.jar file in your 4.0.1 ‘install?’
You might want to post this over onto the OTN forums as well.