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.

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

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

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

    • 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. 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. 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. Parley Kennelly Reply

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

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

    • 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 thatjeffsmith Cancel Reply