SQL Editor Code Templates: A Must-Know Trick

thatjeffsmith SQL Developer 40 Comments

Tell Others About This Story:

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…

Tell Others About This Story:

Comments 40

  1. 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 “@”

  2. 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.

    1. thatjeffsmith Post
      Author
  3. Hi Jeff
    Can you explain how to use #time#, #date#, …
    I tried #time#, “time”, ‘time’, [#time#], [“time”], [‘time’]

    But nothing happened : (

    1. thatjeffsmith Post
      Author
      1. 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

  4. 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! 😀

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. 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???

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  5. 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

    1. thatjeffsmith Post
      Author
  6. 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.

    1. thatjeffsmith Post
      Author
  7. 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.

    1. 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

  8. 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.

    1. thatjeffsmith Post
      Author
  9. 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 ‘]’?

    1. thatjeffsmith Post
      Author
  10. 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!

  11. 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? 🙂

  12. 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.

    1. 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?

        1. thatjeffsmith Post
          Author
          1. 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

          2. thatjeffsmith Post
            Author

            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.

          3. 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.

          4. thatjeffsmith Post
            Author

Leave a Reply

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