Updated 17 April, 2020: This feature now supports AutoReplace

Oracle Docs just underwent a facelift, and I was testing to make sure that the code insight feature in SQL Developer was still able to pick out our code samples. Good news, it still works!

But, I was getting distracted by a bit of code that LOOKED like it was DOCS related, yet wasn’t…

You know the highlighted text isn't coming from the DOCS because of the absence of the Docs graphic to the left...
You know the highlighted isn’t coming from the DOCS because of the absence of the Docs graphic to the left…

So if it’s not coming from the docs, where is it coming from? Well, I knew where, but I activated it just to make sure.

Do you see what I see?
Do you see what I see?

Wait, that one bit of text is highlighted, what happens if I start typing?

Yes, You Can Define the Curpos in a SQL Editor Code Template!

I’m using an exclamation point because I’m excited. I’m excited because I’ve proven again that I’m an idiot. I’ve been asked this question a dozen times or so and I’ve failed to realize how the answer was staring at me in the face.

So after seeing the answer in the create procedure template, I’ve used it to update my SELECT * FROM template:

Bracket the text...
Bracket the text…

And to prove that it works as the people have been asking for the past many years:

Wait Jeff, you HATE SELECT * FROM queries!
Wait Jeff, you HATE SELECT * FROM queries!

Yes, I hate SELECT *.

So let’s fix that.

Ahhh, better.
Ahhh, better.

The ‘magic’ keyboard sequence

Ctrl+Spacebar – the same key sequence to get the code insight assistance will replace the template with the code. Thanks to Nate for bringing it to my attention that I left this key bit out…

But Jeff, I don’t want to ‘invoke’ this, it should JUST happen automatically

You can enable this behavior. So as a template name is recognized, if you hit tab, spacebar, or a newline, the editor will automatically replace your text.

If you enable this, you’ll ‘lose’ the parameter, overstrike feature

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

47 Comments

  1. Pawel Müller Reply

    Hi Jeff,

    I’m at 20.2 now and when using autoreplace the editor doesn’t jump to the [(placeholders)]. So if I type `sel ` with a space, I see my Select-template, but by Placeholder [(Table)] is not highlighted. Is this a know bug?

    The placeholders like #time#, #date# also don’t work, yet. It would be so great to have that 🙂

    cheers
    Pawel

    • Uncheck ‘autoreplace’ and you’ll get the highlighted placeholders – you have to pick one or the other.

  2. When I try to create new template f. ex.:
    *time*, #time#, “time”, ‘time’, [#time#], [“time”], [‘time’]
    as a result i have:
    *time*, #time#, “time”, ‘time’, [#time#], [“time”], [‘time’]

    But I would like to have:
    01-01-2018, #time#, “time”, ‘time’, [#time#], [“time”], [‘time’]
    or
    *time*, 01-01-2018, “time”, ‘time’, [#time#], [“time”], [‘time’]
    etc.

    • Upsss..I mean:

      01:30, #time#, “time”, ‘time’, [#time#], [“time”], [‘time’]
      or
      *time*, 01:30, “time”, ‘time’, [#time#], [“time”], [‘time’]
      etc.

  3. Hi thatjeffsmith

    Is it some plan to debug /rebuild code template ?
    It looks like you forgot about this option : (

    • I have no open bugs on this feature. Can you share a test case I can use to reproduce your issue?

  4. Peter Ocelka Reply

    Hi Jeff,

    Not sure if it is a bug, but in 17.2 if I define template as:
    ID: @d
    template: select /*+ parallel(a,12)*/

    it does not work in the editor. Seems like it has a problem when id begins with “@”

  5. Mikhail Velikikh Reply

    Hi Jeff,

    What characters can we use in our SQL Editor Code Templates?
    I tried to create a code template that used “s*” for “select * from ” but it did not work – it did not expand to anything. Neither automatically, nor manually using CTRL+Space. I also played around the “Auto Replace” checkbox. It did not help either.
    “sa”, “s_” for “select * from ” worked, though.
    I have looked into the SQL Developer documentation but I did not find any restrictions about what characters I could use.
    Could I use SQL Developer for mappings with “star”( * )?

    I am using
    SQL Developer Version 4.2.0.17.089 Build 17.089.1709 for Windows.

    I am aware about Auto-Hotkey or other similar solutions to provide such mappings, but I wish I could store my mappings in one place.

  6. Hi Jeff
    Can you explain how to use #time#, #date#, …
    I tried #time#, “time”, ‘time’, [#time#], [“time”], [‘time’]

    But nothing happened : (

    • I’ve never been told how that’s supposed to work. As far as I’m concerned they’re unusable.

    • Hi Jeff,
      that’s too bad. Just wanted to ask how those work myself. It would be of course great to have them working. I have a code Template wich add a comment line and this should have the current date in it.

      In generell it would be great have expressions that calculate the result on the fly in the code templates. Similar to the functionality in let’s say TextMate. Are there any plans for such a feature?

      thx, Pawel

  7. I’d love the option to have it just auto-expand after typing the space, rather than having to press ctrl+space? I.e. ssf [space] expands to “Select * From”. I know it’s not everyone’s cup of tea so if it was optional then that would be super! 😀

    • For 4.2 it’s indeed an option, just check the ‘auto replace’ option at the bottom of that preferences page.

  8. Hi,

    Ctrl + Space is not working..im using Version 1.5.5 and I cannot update also.

    • Karthick

      Hi,

      Thanks for the reply.

      In v1.5.5 then why they provided the option to setup the code template if that will not work???

    • I could spend the 10 minutes to install v1.5.5 and see what’s what, OR, you could spend the 10 minutes to install v4.1.5.

      I think the advantage is all yours.

  9. Is there a way to export your SQL Editor Code Templates from one PC to another?

    • just zip up your AppData folder and move it over – you’ll get everything. everything but your connections w/passwords – those have to be moved via the UI

  10. Hello Jeff,

    What do you do to expand ‘*’ to column names at the very end. I am using 4.1.3, when I take cursor to the ‘*’, it doesn’t do any thing.

    Thank,
    Vivek

  11. Thanks Jeff for an useful article.
    I have got a small issue, hope you can help. I am on 4.0.0.13 version.
    Whenever I type ssf, then ctrl+space, I am getting getting the “select * from ;”, which is fine. The issue is, I am getting a space(white space) before “select” keyword.
    Not sure where this is coming from, I checked my templates and they don’t have any space.
    Any help would be appreciated.

  12. I just installed 4.1.3 this week and noticed this feature, but am unable to get it to work today? Any suggestions as to why it stopped working? I am connected to a 12c db.

    • The sql expand popup works if I open a new worksheet from an existing 12c connection but does not if I open an existing worksheet and then connecting to a 12c connection. Is there a way to make it work in both instances?

      Thank you,
      David

  13. Hi Smith,
    The Code template is not working for me.
    Am using windows 7 and the developer version : 3.2.20.09.

    Note: when am typing ssf and ctrl+space, its not connecting to that code template. Simply, its standing like ssf.

    Reply ASAP.

    • I’ll try it in v3.2 when I get to my computer this morning.

      3.2 is several years old, please get version 4.1.3 and try there.

  14. In 4.0.2.15, you can use variables in a template. For example:

    SELECT [variable0]
    FROM [variable1]

    It would be nice to have the variable auto-populated if it is used more than once. For example:

    [table].title || ‘ (‘ || [table].[column] || ‘)’ [alias]

    the value supplied for [table] would be automatically inserted into the second instance of [table].

    It would also be nice to be able to use the ‘[‘ character in the code itself:

    [table].title || ‘ [‘ || [table].[column] || ‘]’ [alias]

    this causes havoc with the replacement logic. Is there a way to escape the ‘[‘ and ‘]’?

  15. What are you pressing to expand “ssf” once the template is setup?

    Thanks,
    Nate

  16. Alonso Roman Reply

    I love that bracket thing… Just one thing to be perfect, is it possible load templates from a file?. I mean, when you are working through a team, it is good that this kind of stuff can be shared and being updated by a file. Is there any way to load templates from files in SQL Developer?

    Thank you in advanced and thank you for this trick!

    • Hi!

      Have you been able to sort this “shared settings” thing out?

      I found a solution for sharing snippets (put AddVMOption -Draptor.user.snippets=UserSnippets.xml into sqldeveloper.conf) and wondered if code templates may be shared the same way – without redirecting the whole user/home folder for everyone.

  17. Andrew Merton Reply

    Great tip Jeff – good to know it’s there now; also, that formatted templates wotk as well…

    Did you notice the extra “]” in the CP template? 🙂

  18. Great tip! Thanks. By the way, this post reminded me of one other thing I am missing in sqldev. Is there any way to see the list of columns when expanding * sorted by column_id instead of column_name? I miss this funcionality in other places as well, like when filling the insert into statement with column names.

    • Sorry, I wrote this comment without the SQL Developer next to me and apparently the * is indeed expanded with columns sorted by column_id not the column name. But is it possible to have a list of columns when writing “insert into table_name(” and then doing the ctrl+spacebar also sorted by column_id?

    • Mouse over the * – it will then show the columns. This is a new feature for 4.0, so you’ll need that version or higher.

    • Would be very nice when the replacement of the * would reflect the sort order I used to select the columns (by strg+click in version 4.1.3) instead of using the “normal” order.
      Thx in advance,
      Dirk

    • if you want to be in control of the column order, you pretty much have to use ctrl+select and then drag-n-drop over to the SELECT in the worksheet from the Tree. If you use the * – explode feature, it’s always going to be alphabetized.

    • and exactly this would be the “dot on the i” (as we say in Germany :-)) when on *-explode feature (and also on explain-window Shift+F4 and on tree) there would be a (context menu) option (or by drag&drop) to get a comma seperated list of selected columns in order of selection.
      In tree view I didn’t find the option to ONLY get the comma seperated column list but only a complete SELECT.

  19. Useful information shared..I am very happy to read this article..thanks for giving us nice info.Fantastic walk-through. I appreciate this post.

Reply To Alonso Roman Cancel Reply