Oracle Raptor – Using Snippets (and adding User Defined Snippets) for quicker SQL and PL/SQL coding

The new Raptor tool for Database Development contains a feature called Snippets. Snippets are predefined fragments of SQL or PL/SQL code that you can easily include in a piece of SQL or PL/SQL you are editing. The Snippets are collected in various categories, presented in a Snippets Window from where they can be dragged and dropped into the Editor window. In this article I will show how you can add your own snippets to the set of predefined ones.

Note: I have tried and tried but I cannot paste the snippet any other place than at the end of the code. I can only assume that this is an Early Adpoter release glitch.

After pasting the Snippet – relocating it –  the code looks like this:

Now we have to complete the Snippet, but at least it got the FOR LOOP syntax right for us.

Adding New Snippets to the Snippets Window

I have been looking around to see how I can add Snippets to the base collection in Raptor, just like we can add Code Fragments in JDeveloper. However, I was unable to find that feature. So for a short while I feared we were stuck with what the Raptor team provides us with. Then my almost innate drive to investigate APIs, extensions, configuration options and the like had me looking around. Before too long, I got to the directory RAPTOR_HOME\\jdev\extensions. This directory contains the archive oracle.dbdev.snippet.10.1.3.jar. That jar file in turn looks like this:

The file snippets.xml contains the Snippets that are shown in the Snippets window:

The obvious next step is now of course to add a number of my own snippets to this snippets.xml and add the changed file to the jar. Let’s do some Analytical Function snippets:

  <group category=”Analytical Functions” language=”PLSQL”>
<snippet name=”ROW_NUMBER()” description=”Include a ROW_NUMBER() expression in your SELECT statement. ROW_NUMBER calculates the unique position of a record within a subset of records – created  by partitioning on the indicated column – ordered by the specified column. “>
<code><![CDATA[row_number() over ( partition by col1
order by col2 desc
) rn]]></code>
</snippet>
<snippet name=”MAX()” description=”Include an Analytical MAX() expression in your SELECT statement. MAX returns the highest value from a subset of records – created  by partitioning on the indicated column. If the partition is ordered then the maximum is in fact a running Maximum, calculated only over the records preceding the current record in the partition.”>
<code><![CDATA[max(column) over ( partition by col1
order by col2 desc
) ld]]></code>
</snippet>
<snippet name=”LEAD()” description=”Include a LEAD() expression in your SELECT statement. LEAD returns a column value from a record that follows the current record in the subset – created  by partitioning on the indicated column – ordered by the specified column.”>
<code><![CDATA[lead(column, number_of_records, default value) over ( partition by col1
order by col2 desc
) ld]]></code>
</snippet>
</group>

I have added this fragment to snippets.xml, added the updated snippets.xml to the jar file and restarted Raptor. I see no effect at all. None whatsoever. That is somewhat disappointing- I was so sure of it!

As it turns out, I was almost correct. It just was the wrong snippets.xml file. The one we need to edit is in RAPTOR_HOME\jdev\system\oracle.dbdev.snippet.10.1.3. Now after we restart Raptor, the Snippets window looks like:

Dragging, dropping and slightly repositioning the new LEAD() snippet gives us:

Filling in the missing pieces, our query and its result:

Now I am wondering: will there be a lively trade in Snippets? Snippets for frequently occuring SQL constructs, useful sample queries, best practice PL/SQL fragments etc. And will Oracle provide a supported way of maintaining the snippets?