17 August 2006

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'?>
<group category="Sue's Repeated Queries" language="PLSQL">
<snippet name="ALLEmployees"
description="This is just selecting columns from EMPLOYEES">
<![CDATA[SELECT employee_id, first_name, last_name, email,
hire_date, job_id, salary,
commission_pct, manager_id, department_id, last_name_id

FROM hr.employees;]]>
<snippet name="Sue's Locations Countries Regions Join"
description="This does the inner join">
<![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;]]>
<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.">


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!


Anonymous said...

Hi Sue,
Thanks for you post. Based on your post, I was able to create a snippet for Regular Expressions.

Thanks again for all your posts about SQL Developer.


Sue said...

Glad to have been of help, Ramesh. We're going to be announcing a new website for SQL Developer next week, where you can add your snippets and share them with others. I'll announce it here and on the OTN forum.


Anonymous said...

I was planning to paste the file on the SQL Developer Forum, but the website is a much better idea.


Anonymous said...

I would note that to include a path to a file on a shared drive, you'll need the fully resolved UNC (ie //servername/directory/snippets.xml) not the drive letter mapping. Other than that, it works great.

Sue said...

Of course, now you can use SQL Developer 1.1 and use the Snippets Editor to add your own snippets without the hints in this blog entry.


Frenske said...

Hello Sue,

i'm working at vodafone netherlands and we all use sql developer here. great tool! much better then TO@D :).

thanks for the hints on the snippet part. i've got a couple of snippets i use every day, finally got them in the sql dev!

Greets Francois

Sue said...


Nice to hear you have some success. Have you tried 1.1 yet? In 1.1 you can easily add snippets using the dialog in the product.


Anonymous said...

Hello Sue,
Thanks for the tip, but
in version 1.5.1 this does not
There is a menu item 'save snippet',
but how can i save the snippet to
a file on a network in order that
others can also use it?