I’ve recently spent a few hours debugging some user defined reports. A post on how to add trace/disconnect context menus to a session report required that I figure out what session my worksheet was running under.

The code was easy…

SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

…but I’m lazy.

So this:

Type a bit, hit the keyboard, wham-O!
Type a bit, hit the keyboard, wham-O!

The Setup

Open your preferences and go to the SQL Editor Code Templates.

The snippet is 'programmed' into the 'ID', not the 'Template'
The snippet is ‘programmed’ into the ‘ID’, not the ‘Template’

As you code these, you recall them from what you label them via the ID, not the code itself.

And then you activate them in the editor via Ctrl+Spacebar.

Don’t like this keyboard sequence? Change it!.

Not working for you?

Upgrade. I found that a few of mine wouldn’t work, and we found a bug. It’s been fixed in v4. Another reason to go upgrade, like…today.

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

10 Comments

  1. Avatar

    Templates don’t work (Ctrl + Spacebar does nothing) in SQL scripts (opened in SQL Developer, by dragging the file into), only in new worksheets. I’m on 18.2

    • thatjeffsmith

      assign a connection to your script/worksheet – for some reason this feature is tied to connections (probably shouldn’t be though)

  2. Avatar

    Hi Jeff,
    Thank you for sharing your knowledge with us. I have learned a lot of cool new things about sql developer!

    I have the same problem like PP one comment above. If you enter a “special” character like “ü” in a template ALL stored templates disappear without any error message.

    Do you know if this bug will be fixed soon? Unfortunately I have lost a lot of stored teplates. Do you know the filename where the templates are saved? I can make a backup before I enter a new template.

    Sorry, English is not my mother tongue 🙁

    Kind regards,
    Markus

    • thatjeffsmith

      Egads, that’s no good. I logged that as a P2, and we’ll get it fixed for v4.1 I don’t think your templates are lost, I just think the character is screwing up our XML parser.

      Go into your $USER$ directory, on windows it would be AppData\Roaming\Profiles\SQL Developer\CodeTemplates.xml

      Edit that file, take out the umlaut, and you should be good to go again.

  3. Avatar

    Hello,

    I noticed a strange bug in code templates:
    enter a £ in the template and then save it, click ok, then reopen the preferences on code templates. They disappeared ?

  4. Avatar

    Hello,

    I like templates to create packages body/specs with company headers, svn tags, etc.
    I see we can use square brackets for “variables placeholders”, are there any other secrets?

    Thanks

  5. Avatar
    Parley Kennelly Reply

    Jeff,
    You mentioned the new version coming. Can you give us a time line?

  6. Avatar

    Hi Jeff, this is cool, but how do you stop it auto-formatting the template when it’s inserted?

    • thatjeffsmith

      Wait till our next major version, we’re going to be turning that behavior off. In the meantime you’ll want to tweak your formatting options such that it gets as close as you can to what you want.

Reply To Parley Kennelly Cancel Reply