0 
0 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?
Alex (or anyone else),
Could you give any clues as to how you did that.
I tried, but got a bit stuck – I installed PackageManager, but didn’t get much further
I’ve packaged Raptor in .app in order not to start the shell everytime
For all you OS X adepts (and like me, running 10.4), that having trouble to locate a 1.5 JDK (thanks apple for the fast email service from your developer site 🙁 ), just follow these directions:
1. Install java 1.5, you can find it here: http://www.apple.com/support/downloads/java2se50release3.html
2. Append SetSkipJ2SDKCheck true to /path/to/raptor/jdev/bin/raptor.conf (please mind, no spaces in the path, this is a known bug)
3. Start the raptor file (sh raptor in /path/to/raptor ) Enter /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home as J2SE home and you are set to go.
This early build is not supported on Mac, but it does work like a charm. You might need to switch to java 1.5 with the java preferences tool which can be found in /Applications/Utilities/Java/JS2E 5.0
Credit for this note goes to Thomas Barry, as posted on the otn forum. As Mac adept this seems to be the first serious Oracle database tool (next to sqlplus ;)), next to Aqua data studio (which has not so great pl/sql support).
If you were woundering where you could download it (Raptor) —>
http://www.oracle.com/technology/products/database/project_raptor/index.html