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!