There’s nothing worse than the ‘white screen of panic.’ You’re faced with the prospect of actually writing some code from scratch. Or even worse, you’re doing a presentation or demo, and everyone gets to see what a horrible typist you are.

Yes, this is about Snippets, and Snippets are cool. But if you’re a keyboard person, then you’re gonna like SQL Templates even better. Go read this and come back. -TJS 3/13/15

The white screen of panic

Yes, you could browse your SQL History, but that could contain several hundred or even thousands of statements. And let’s face it, you probably don’t recognize code you wrote even a few days ago when you see it.

Wouldn’t it be nice if you could take your favorite statements or scripts, and save them to recall later? Even better, let me name and document them?

Enter the Snippets

Access your snippets via the View menu. There’s a few included ‘out of the box,’ but you’ll want to add your own to the ‘Custom’ category.

Access the Snippets from the View menu

Add a Snippet

The easiest way to add a snippet is to select your code and right-click. Choose ‘Save as Snippet…’

You can do this for a single statement or multiple statements (scripts)

Define the Snippet

Place it into the proper category, give it a name, and define the tool-tip that will appear if you mouse-over the snippet.

The tool-tip is optional

Snippets in Action

Now when you get back to the white-screen-of-panic, remember your trusty statements and scripts are accessible via the Snippets panel. Select your snippet and drag and drop (d-n-d) it into the worksheet or Procedure Editor. The d-n-d will respect any code you already have in your editor, meaning you can drag it exactly where you want to drop it and preserve your existing code.

Power Tip: Did you know you can share your favorite snippets with other SQL Developer users at the Exchange? Browse and download your favorite snippets or upload your own here. We don’t support or endorse these snippets, so use at your own risk.

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.

23 Comments

  1. Scott Kennedy Reply

    It’s a shame there is no way to search snippets makes it near impossible to use them effectively once you have more than a handful.

  2. Hi I have just upgraded to latest SQL Developer 21.4.3
    Snippets have disappeared
    No Snippets under “View” as before
    No “Save as Snippet”
    Tried “Reset Windows to Factory Settings” under the Window menu
    Did not bring back Snippets

  3. Edoardo Carbone Reply

    Hi, I’m using SQL developer on mac os Big Sur. Do you know what is the path where I can find UserSnippets.xml? Thank you so much!

  4. Stephen OGorman Reply

    Hi Jeff: I have just upgraded to SQL Developer 20.2.0
    Snippets have disappeared
    No Snippets under “View” as before
    No “Save as Snippet”
    Please advise ASAP

  5. Peter van Langevelde Reply

    It would be nice if you can pass on the value of a parameter when invoking a snippet or adding a code template, eg to replace the [(parameter)] to the actual value you want to use.

    • You can almost do this with a code template – you can have a portion of the code highlighted for strikeover once it goes into the editor.

  6. If I am moving to another pc is there a way of taking my snippets to the new pc? Thanks

    • All of your files can be found in the AppData folder for your Windows user. Look for ‘UserSnippets.XML’

  7. I use snippet a lot. I found it is quite hard to find and edit a snippet since I have a long list for each category. Is there an easy way to search my snippet?

  8. Raj Phaniraj Reply

    Could a preference be added to let Snippets default to a user selected category ? In my case I use Custom the most, and have to change the category to Custom almost every time I am using a snippet.

  9. Hi Mr. Smith,

    Your blog came up during a SQL Fundamentals I class at Austin Community College. I <3 it! One of the best recommendations our instructor has made.

    My question is this:

    I'm kind of a pacifist and by nature write SQL in all lower case (unless I'm writing something I intend to share with others in which case I try to use CAPS when writing keywords and commands).

    Aside from writing my own version of snippets, is there a way to re-format existing ones so that they are more aesthetically pleasing (all lower case, all upper case, camelCase, whatever)?

    I suppose I can F7 after inserting the snip, just wanted to ask if there was any way to re-format prior to inserting it.

    • You want all of the snippets set to lowercase?

      I have an easier solution, I THINK

      Tools – Preferences – Code Editor – Completion Insight
      Chase Case As You Type – lowercase

      That should satiate your pacifist nature, I hope 🙂

      It won’t affect quoted strings, but will touch everything else – not just the snippets.

      Your other option is to open the snippets xml file and just lowercase everything using SQL Dev or your favorite text editor.

  10. Hi Jeff.
    I’m an Oracle developer spending much time on SQL Developer.
    I really like this tool but i miss one thing from the tool i used before (PL Dev, not to name it) and it is the ability to affect an “accelerator” for a snippet.
    For exemple when you type “s*” and press the space bar after it, the “s*” turns into “select * from “. It can save so much typing !
    They have the same thing in eclipse (e.g. : syserr = system.Err.println..)
    Do you thing it can be possible to implement this feature in SQL Dev ?
    I mean it can just be a property of a snippet.
    Anyway, keep up the good work on SQL Dev, cause it’s getting more and more powerfull and i really appreciate the product and your blog.
    Thanks

    • Do you mean like what we have for SQL Editor Code Templates? You invoke them with the auto-complete keystroke, which is ctrl+spacebar by default. They’re defined in the Preferences under the Database page.

      Also, thanks for not naming ‘the other tool’ 🙂

  11. Ah, snippets, quite an underused feature of SQL Dev. I’ve been using Snippets since my earliest SQL dev days – especially so since I had a bunch of SQL statements that I’d run to verify my data before starting a humongous data conversion job.

    • Jeff,

      I think your blog post about Snippets is good enough 😉 The only other tips I’d suggest is

      – If you’re using the snippets often, it’d be a good idea to keep the window docked
      – I believe you can also add custom categories – having snippets categorized instead of having them all under “Custom” allows for easy discovery of snippets.
      – The snippets are stored in %appdata%\SQL Developer\UserSnippets.xml file, it’d be prudent to keep the file backed up regularly. Or have a bunch of snippets pre-built and saved in the folder to save time

  12. Hi Jeff

    I have a question…Can I shared your information in Spanish in my blog..? obviously I will write source and put link. Thank you so much I think is very interesting here Colombia.

    • Thanks for the offer Ivan, but I’m not looking to have my content syndicated/translated at this point just yet. I’d be happy to guest post on your blog though, just give me a topic you and your readers would like to know more about!

      And again – thanks for asking. Many folks just grab content they want, and act offended when you ask them to stop.

Reply To thatjeffsmith Cancel Reply