Add Your Own Snippets Today in SQL Developer 1.0

Earlier this week I showed you some screens shots for adding snippets using a menu in 1.1, that's no good to you today, so this entry is devoted to adding snippets manually in 1.0.

Kris has blogged on this before in a few places. His initial entry on this, back in January, referred to raptor files and extensions. The blog entry is still accurate, just that file names have changed, which is why I thought I'd update this manual step for SQL Developer 1.0. Kris goes on later to talk about an extension that he put on SourceForge that "enables support for read only XMLTypes and adds export to excel in native excel in the worksheet". He updated this extension (blog entry 8th March) to support highlighting some text and then adding it to the snippets file through the extension.

So this entry is not about adding an extension, but creating a snippets file and hooking that into your SQL Developer install. I'll break this down into steps.

I'm using SQL Developer 1.0

Step 1. Create a directory for your snippets. I have created a "mysnippets" directory. I have chosen to keep it with my product installation, but there is no reason why you can't keep this file centrally on a separate file server, you just need to direct SQL Developer to where you have placed it.

Step 2. Create a snippets.xml file. If you have a favorite XML editor, use that. I used Textpad!

Step 3. Add the snippet code.

The XML is pretty straight forward. Here is a skeleton of code:

I know, Textpad does not highlight code like that! I used JDeveloper's XML editor, it uses code highlighting and so it's easier to see the structure.

Here is an example of a small snippet file. Once again, I used JDeveloper's XML editor, for the code highlighting. Notice within one Category, i.e.XML tag , I have 2 separate snippets.

The piece of code between the CDATA [] is what is dragged onto the worksheet. Below is that same piece of code to cut and paste into Textpad:

<?xml version = '1.0' encoding = 'UTF-8'?>
<snippets>
<group category="Sue's Repeated Queries" language="PLSQL">
<snippet name="ALLEmployees"
         description="This is just selecting columns from EMPLOYEES">
  <code>
    <![CDATA[SELECT employee_id, first_name, last_name, email,
               phone_number,hire_date, job_id, salary,
  commission_pct, manager_id, department_id, last_name_id
                FROM hr.employees;]]>
  </code>
</snippet>
<snippet name="Sue's Locations Countries Regions Join"
         description="This does the inner join">
  <code>
    <![CDATA[SELECT COUNT(locations.city) "Number of Cities",
  regions.region_name regions
                FROM(locations INNER JOIN countries USING(country_id))
                INNER JOIN regions USING(region_id)
                GROUP BY regions.region_name ORDER BY 1;]]>
  </code>
</snippet>
</group>
<group category="More Character Functions" language="PLSQL">
<snippet name="REPLACE"
         description="REPLACE lets you substitute one string
                     for another as well as to remove character strings.">
  <code>
    <![CDATA[REPLACE(expr,search_string,replacement_string)]]>
  </code>
</snippet>
</group>
</snippets>

So now you have the snippets.xml file. The last step is to tell SQL Developer about it.

Step 4. Update the sqldeveloper.conf file. You'll find this in the sqldeveloper/jdev/bin directory.

Once again, using Texpad to edit the sqldeveloper.conf file, add the line of code below to point to your snippets file.

AddVMOption -Draptor.user.snippets=../../mysnippets/snippets.xml

It's important to note that the path depends on where you have the file. You can put the full path here. Note too that any path separators must be # UNIX style forward slashes '/', even on Windows.

Step 5. Test it!

Handling Snippets: Sneak Peak into 1.1

Here's another sneak peak into what's coming in SQL Developer 1.1.

In 1.0 you can add your own snippets. Kris gives some suggestions on his blog on how to go about it. There are a other notes about suggesting alternative approaches. We're not happy about some of these i.e. The suggestion to yank the XML file from the jar, update it with the snippet and replace the file in the jar. I have done it myself and it works and is easy. It's not recommended, because if you get into the habit of essentially hacking the jar files you can break stuff. Simpley out, editing the .jar files is not supported. Kris does give you a way to do it legally.

Anyway, to save all that hassle, we've given you a small menu and dialog

The menu shows the Add and Edit icons in the Snippet window. Below is the dialog for adding a new category and snippet.

So you can use the drop list and add a new snippet to any existing category, or you can create your own category by just typing it in.