Customising gedit for PL/SQL on Ubuntu 9.10

As I’ve said before, when it comes to doing serious Database programming, I prefer to work in SQL*Plus.

Yes, SQLDeveloper ( and Toad for that matter) are great for looking at stored program units, running ad-hoc queries etc, but I find there’s no substitute for being “close to the metal” when it comes to in-depth or complex programming in PL/SQL.

Apart from anything else, the feedback you get at the prompt is the actual error, and not masked by something the IDE is objecting to.
Line numbering also helps a lot. If the compiler reports an error at line 100, I want to be able to go to line 100 in my code directly, no messing. Text editors tend to do this stuff really well.

At work, being stuck on Windows, I have to rely on the trusty Textpad, which is perfectly adequate.
Fortunately, when I get home, it’s Ubuntu all the way. This means getting to play with another of those unobtrusive Linux gems – gedit.

There’s a really good article on how to customise gedit on tuxcoder.
But before you go running off to read the musings of a proper Linux expert, it’s probably worth me mentioning what I’m covering here :

• making sure you’ve got all of the gedit plugins
• adding file extensions for gedit to recognise as SQL or PL/SQL files
• adding syntax highlighting for PL/SQL code

Getting gedit plugins

Whilst gedit comes with some plugins as part of the standard Ubuntu distro, there are even more goodies available in the gedit-plugins package.
So, if like me, you don’t already have this package, you can get it in the usual way – i.e. either by opening terminal and typing :

sudo apt-get gedit-plugins

or by using Synaptic Package Manager

gedit-plugins in Synaptic

Once the package is installed, open gedit and go to Edit/Preferences and click the Plug-Ins tab where you can view the resulting selection of goodies…

gedit Plugins dialog

To enable the plugin, just click the checkbox next to it.
I’m not going to go into detail about what each plugin does ( that’s covered by the Tuxcoder article) but I do have to mention two of them.

One is the Embedded Terminal, which makes a terminal session available in the pane below the main editing area. This allows you to run your code, see any errors, then edit, save and re-run without having to switch applications.

The other is the Code Comment plugin which enables you to comment out or uncomment lines or blocks of code just by using a keyboard shortcut ( CTRL + M to comment out, SHIFT CTRL M to uncomment). That’s just so cool.
Getting a bit misty-eyed and geeky there. Come on Mike, focus !

Adding file extensions for PL/SQL files

Not all PL/SQL ( or even SQL ) scripts I write are in files with .sql as the extension.
Like many people, if I’m writing a package, I’ll save the script in a file with the format of package_name.extension. Obviously, the package header and package body have the same name, so I need to distinguish between the files by means of the extension. So, the next step is to get gedit to recognise files with these extensions as being SQL or PL/SQL files.

While we’re at it, we may as well specify all of the following file extensions as being PL/SQL files :

• *.fnc – for database functions
• *.prc – for database procedures
• *.pks – for package specifications
• *.pkb – for package bodies

Like Textpad, gedit implements syntax highlighting by means of language definition files…and it has them in spades. 73 of them, in fact.
You can find these files in /usr/share/gtksourceview-2.0/language-specs .
The definition file we’re interested in is sql.lang.

It’s worth noting that at this point, I got a bit careful and made a backup of the file before I started messing about with it…just in case.
In Terminal :

cd /usr/share/gtksourceview-2.0/language-specs
sudo cp sql.lang sql.bak
sudo gedit sql.lang

We will now have a copy of the original file in sql.bak whilst we’re playing with it.

Once in the file, we need to define these extensions in the sources section ( that’s the first section in the file) :

<language id="sql" _name="SQL" version="2.0" _section="Sources">
  <metadata>
    <property name="mimetypes">text/x-sql</property>
    <property name="globs">*.sql</property>
    <property name="globs">*.fnc</property>
    <property name="globs">*.prc</property>
    <property name="globs">*.pks</property>
    <property name="globs">*.pkb</property>
    <property name="line-comment-start">--</property>
  </metadata>
<language id="sql" _name="SQL" version="2.0" _section="Sources">

Save the changes, open a file with the appropriate extension in gedit and it should now be highlighted in the same way as a .sql file.
The sql.lang file is pretty comprehensive out of the box. Evert Verhellen, the author, has not only included all of the ANSI standard SQL commands but has added keywords for specific vendor implementations such as DB2 and, fortunately for us, Oracle.
Not only that he’s included the SQL*Plus keywords as well.
Mr Verhellen, if you’re not already a famous Belgian, you jolly well should be !

Adding PL/SQL keywords

After saying that, it seems almost impolite to ham-fistedly hack this exquisitely assembled creation.
Looking through the file, it would appear that it’s possible to use something that looks suspiciously like POSIX regular expression syntax.
Unfortunately for me, regular expression syntax is a bit like JavaScript – something I don’t use for ages, then have to get up to speed with in a short, concentrated period of time. Then, when I’m just getting back into the swing of things, I don’t have the need to use it again for ages.
So, I’ve played around a bit with the regular expressions in the sections I’ve added, but in the main, I’ve played safe and just included the relevant keyword unadulterated.

Incidentally, I went here for a list of PL/SQL keywords for Oracle 10g Release 2. This page makes a distinction between keywords and reserved words so I’ve followed that lead and included them in separate sections in the file.

    <context id="pl-sql-reserved-words" style-ref="keyword">
      <keyword>ALL</keyword>
      <keyword>ALTER</keyword>
      <keyword>AND</keyword>
      <keyword>ARRAY</keyword>
      <keyword>ARROW</keyword>
      <keyword>AS</keyword>
      <keyword>ASC</keyword>
      <keyword>AT</keyword>     
      <keyword>BEGIN</keyword>
      <keyword>BETWEEN</keyword>      
      <keyword>BY</keyword>      
      <keyword>CASE</keyword>      
      <keyword>CHECK</keyword>      
      <keyword>CLUSTER(S)?</keyword>
      <keyword>COLAUTH</keyword>
      <keyword>COLUMNS</keyword>
      <keyword>COMPRESS</keyword>                        
      <keyword>CONNECT</keyword>
      <keyword>CRASH</keyword>
      <keyword>CREATE</keyword>
      <keyword>CURRENT</keyword>
      <keyword>DECIMAL</keyword>
      <keyword>DECLARE</keyword>
      <keyword>DEFAULT</keyword>
      <keyword>DELETE</keyword>
      <keyword>DESC</keyword>
      <keyword>DISTINCT</keyword>
      <keyword>DROP</keyword>
      <keyword>ELSE</keyword>
      <keyword>END</keyword>
      <keyword>EXCEPTION</keyword>
      <keyword>EXCLUSIVE</keyword>
      <keyword>EXISTS</keyword>
      <keyword>FETCH</keyword>
      <keyword>FORM</keyword>
      <keyword>FOR</keyword>
      <keyword>FROM</keyword>
      <keyword>GOTO</keyword>
      <keyword>GRANT</keyword>
      <keyword>GROUP</keyword>
      <keyword>HAVING</keyword>
      <keyword>IDENTIFIED</keyword>
      <keyword>IF</keyword>
      <keyword>IN</keyword>
      <keyword>INDEX(ES)?</keyword>
      <keyword>INSERT</keyword>
      <keyword>INTERSECT</keyword>
      <keyword>INTO</keyword>
      <keyword>IS</keyword>
      <keyword>LIKE</keyword>
      <keyword>LOCK</keyword>
      <keyword>MINUS</keyword>
      <keyword>MODE</keyword>
      <keyword>NOCOMPRESS</keyword>
      <keyword>NOT</keyword>
      <keyword>NOWAIT</keyword>
      <keyword>NULL</keyword>
      <keyword>OF</keyword>
      <keyword>ON</keyword>
      <keyword>OPTION</keyword>
      <keyword>OR</keyword>
      <keyword>ORDER</keyword>
      <keyword>OVERLAPS</keyword>
      <keyword>PRIOR</keyword>
      <keyword>PROCEDURE</keyword>
      <keyword>PUBLIC</keyword>
      <keyword>RANGE</keyword>
      <keyword>RECORD</keyword>
      <keyword>RESOURCE</keyword>
      <keyword>REVOKE</keyword>
      <keyword>SELECT</keyword>
      <keyword>SHARE</keyword>
      <keyword>SIZE</keyword>
      <keyword>SQL</keyword>
      <keyword>START</keyword>
      <keyword>SUBTYPE</keyword>
      <keyword>TABAUTH</keyword>
      <keyword>TABLE</keyword>
      <keyword>THEN</keyword>
      <keyword>TO</keyword>
      <keyword>TYPE</keyword>
      <keyword>UNION</keyword>
      <keyword>UNIQUE</keyword>
      <keyword>UPDATE</keyword>
      <keyword>USE</keyword>
      <keyword>VALUES</keyword>
      <keyword>VIEW</keyword>
      <keyword>VIEWS</keyword>
      <keyword>WHEN</keyword>
      <keyword>WHERE</keyword>
      <keyword>WIDTH</keyword>
    </context>
                    
    <context id="pl-sql-keywords" style-ref="keyword">
      <keyword>A</keyword>
      <keyword>ADD</keyword>
      <keyword>AGENT</keyword>
      <keyword>AGGREGATE</keyword>
      <keyword>ARRAY</keyword>
      <keyword>ATTRIBUTE</keyword>
      <keyword>AUTHID</keyword>
      <keyword>AVG</keyword>     
      <keyword>BFILE_BASE</keyword>
      <keyword>BINARY</keyword>
      <keyword>BLOB_BASE</keyword>
      <keyword>BLOCK</keyword>
      <keyword>BODY</keyword>
      <keyword>BOTH</keyword>
      <keyword>BOUND</keyword>
      <keyword>BULK</keyword>     
      <keyword>BYTE</keyword>
      <keyword>C</keyword>
      <keyword>CALLING</keyword>
      <keyword>CASCADE</keyword>
      <keyword>CHAR</keyword>
      <keyword>CHAR_BASE</keyword>
      <keyword>CHARACTER</keyword>
      <keyword>CHARSETFORM</keyword>     
      <keyword>CHARSETID</keyword>
      <keyword>CHARSET</keyword>
      <keyword>CLOB_BASE</keyword>
      <keyword>CLOSE</keyword>
      <keyword>COLLECT</keyword>
      <keyword>COMMENT</keyword>
      <keyword>COMMIT</keyword>
      <keyword>COMMITTED</keyword>     
      <keyword>COMPILED</keyword>
      <keyword>CONSTANT</keyword>
      <keyword>CONSTRUCTOR</keyword>
      <keyword>CONTEXT</keyword>
      <keyword>CONVERT</keyword>
      <keyword>COUNT</keyword>
      <keyword>CURSOR</keyword>
      <keyword>CUSTOMDATUM</keyword>     
      <keyword>DANGLING</keyword>
      <keyword>DATA</keyword>
      <keyword>DATE</keyword>
      <keyword>DATE_BASE</keyword>
      <keyword>DAY</keyword>
      <keyword>DEFINE</keyword>
      <keyword>DETERMINISTIC</keyword>
      <keyword>DOUBLE</keyword>
      <keyword>DURATION</keyword>
      <keyword>ELEMENT</keyword>
      <keyword>ELSIF</keyword>
      <keyword>EMPTY</keyword>
      <keyword>ESCAPE</keyword>
      <keyword>EXCEPT</keyword>
      <keyword>EXCEPTIONS</keyword>
      <keyword>EXECUTE</keyword>
      <keyword>EXIT</keyword>
      <keyword>EXTERNAL</keyword>
      <keyword>FINAL</keyword>
      <keyword>FIXED</keyword>
      <keyword>FLOAT</keyword>
      <keyword>FORALL</keyword>
      <keyword>FORCE</keyword>
      <keyword>FUNCTION</keyword>     
      <keyword>GENERAL</keyword>
      <keyword>HASH</keyword>
      <keyword>HEAP</keyword>
      <keyword>HIDDEN</keyword>
      <keyword>HOUR</keyword>
      <keyword>IMMEDIATE</keyword>
      <keyword>INCLUDING</keyword>
      <keyword>INDICATOR</keyword>     
      <keyword>INDICES</keyword>
      <keyword>INFINITE</keyword>
      <keyword>INSTANTIABLE</keyword>
      <keyword>INT</keyword>
      <keyword>INTERFACE</keyword>
      <keyword>INTERVAL</keyword>
      <keyword>INVALIDATE</keyword>
      <keyword>ISOLATION</keyword>     
      <keyword>JAVA</keyword>
      <keyword>LANGUAGE</keyword>
      <keyword>LARGE</keyword>
      <keyword>LEADING</keyword>
      <keyword>LENGTH</keyword>
      <keyword>LEVEL</keyword>
      <keyword>LIBRARY</keyword>
      <keyword>LIKE(2|4|C)</keyword>     
      <keyword>LIMIT</keyword>
      <keyword>LIMITED</keyword>
      <keyword>LOCAL</keyword>
      <keyword>LONG</keyword>
      <keyword>LOOP</keyword>
      <keyword>MAP</keyword>     
      <keyword>MAX</keyword>
      <keyword>MAXLEN</keyword>
      <keyword>MEMBER</keyword>
      <keyword>MERGE</keyword>
      <keyword>MIN</keyword>
      <keyword>MINUTE</keyword>
      <keyword>MOD</keyword>
      <keyword>MODIFY</keyword>     
      <keyword>MONTH</keyword>
      <keyword>MULTISET</keyword>
      <keyword>NAME</keyword>
      <keyword>NAN</keyword>
      <keyword>NATIONAL</keyword>
      <keyword>NATIVE</keyword>
      <keyword>NCHAR</keyword>
      <keyword>NEW</keyword>     
      <keyword>NOCOPY</keyword>
      <keyword>NUMBER_BASE</keyword>
      <keyword>OBJECT</keyword>
      <keyword>OCICOLL</keyword>
      <keyword>OCIDATETIME</keyword>
      <keyword>OCIDATE</keyword>
      <keyword>OCIDURATION</keyword>
      <keyword>OCIINTERVAL</keyword>
      <keyword>OCILOBLOCATOR</keyword>
      <keyword>OCINUMBER</keyword>
      <keyword>OCIRAW</keyword>
      <keyword>OCIREFCURSOR</keyword>
      <keyword>OCIREF</keyword>
      <keyword>OCIROWID</keyword>
      <keyword>OCISTRING</keyword>
      <keyword>OCITYPE</keyword>
      <keyword>ONLY</keyword>
      <keyword>OPAQUE</keyword>
      <keyword>OPEN</keyword>
      <keyword>OPERATOR</keyword>
      <keyword>ORACLE</keyword>
      <keyword>ORADATA</keyword>
      <keyword>ORGANIZATION</keyword>
      <keyword>ORLANY</keyword>
      <keyword>ORLVARY</keyword>
      <keyword>OTHERS</keyword>
      <keyword>OUT</keyword>
      <keyword>OVERRIDING</keyword>
      <keyword>PACKAGE</keyword>
      <keyword>PARALLEL_ENABLE</keyword>
      <keyword>PARAMETER</keyword>
      <keyword>PARAMETERS</keyword>     
      <keyword>PARTITION</keyword>
      <keyword>PASCAL</keyword>
      <keyword>PIPE</keyword>
      <keyword>PIPELINED</keyword>
      <keyword>PRAGMA</keyword>
      <keyword>PRECISION</keyword>
      <keyword>PRIVATE</keyword>
      <keyword>RAISE</keyword>     
      <keyword>SAMPLE</keyword>
      <keyword>SAVE</keyword>
      <keyword>SAVEPOINT</keyword>
      <keyword>SB(1|2|4)</keyword>
      <keyword>SECOND</keyword>
      <keyword>SEGMENT</keyword>
      <keyword>SELF</keyword>
      <keyword>SEPARATE</keyword>
      <keyword>SEQUENCE</keyword>
      <keyword>SERIALIZABLE</keyword>
      <keyword>SET</keyword>
      <keyword>SHORT</keyword>
      <keyword>SIZE_T</keyword>
      <keyword>SOME</keyword>
      <keyword>SPARSE</keyword>
      <keyword>SQLCODE</keyword>
      <keyword>SQLDATA</keyword>
      <keyword>SQLNAME</keyword>
      <keyword>SQLSTATE</keyword>
      <keyword>STANDARD</keyword>
      <keyword>STATIC</keyword>
      <keyword>STDDEV</keyword>
      <keyword>STORED</keyword>
      <keyword>STRING</keyword>
      <keyword>STRUCT</keyword>
      <keyword>STYLE</keyword>
      <keyword>SUBMULTISET</keyword>
      <keyword>SUBPARTITION</keyword>
      <keyword>SUBSTITUTABLE</keyword>
      <keyword>SUBTYPE</keyword>
      <keyword>SUM</keyword>
      <keyword>SYNONYM</keyword>
      <keyword>TDO</keyword>
      <keyword>THE</keyword>
      <keyword>TIME</keyword>
      <keyword>TIMESTAMP</keyword>
      <keyword>TIMEZONE_(ABBR|HOUR|MINUTE|REGION)</keyword>
      <keyword>TRAILING</keyword>
      <keyword>TRANSAC</keyword>
      <keyword>TRANSACTIONAL</keyword>
      <keyword>TRUSTED</keyword>
      <keyword>TYPE</keyword>
      <keyword>UB(1|2|4)</keyword>
      <keyword>UNDER</keyword>
      <keyword>UNSIGNED</keyword>
      <keyword>UNTRUSTED</keyword>
      <keyword>USE</keyword>
      <keyword>USING</keyword>
      <keyword>VALIST</keyword>
      <keyword>VALUE</keyword>
      <keyword>VARIABLE</keyword>
      <keyword>VARIANCE</keyword>
      <keyword>VARRAY</keyword>
      <keyword>VARYING</keyword>
      <keyword>VOID</keyword>
      <keyword>WHILE</keyword>
      <keyword>WORK</keyword>
      <keyword>WRAPPED</keyword>
      <keyword>WRITE</keyword>
      <keyword>YEAR</keyword>
      <keyword>ZONE</keyword>
    </context>

As a result of these additional sections, there are probably some overlaps with stuff that was already here, but as I’ve specified the style-ref as “keyword” (i.e. the same as for the other keyword sections in the file) , this shouldn’t give gedit any problems.

Finally, I need to include these new sections in the include section at the end of the file :

    <context id="sql">
      <include>
        <context ref="oracle-built-in-datatypes"/>
        <context ref="ansi-datatypes"/>
        <context ref="sql-ds-and-db2-datatypes"/>
        <context ref="oracle-supplied-types"/>
        <context ref="text-literals"/>
        <context ref="integer-literals"/>
        <context ref="number-literals"/>
        <context ref="size-clause"/>
        <context ref="unlimited"/>
        <context ref="null"/>
        <context ref="block-comment"/>
        <context ref="line-comment"/>
        <context ref="numeric-functions"/>
        <context ref="character-functions-returning-character-values"/>
        <context ref="nls-character-functions"/>
        <context ref="character-functions-returning-number-values"/>
        <context ref="datetime-functions"/>
        <context ref="general-comparison-functions"/>
        <context ref="conversion-functions"/>
        <context ref="large-object-functions"/>
        <context ref="collection-functions"/>
        <context ref="hierarchical-function"/>
        <context ref="data-mining-functions"/>
        <context ref="xml-functions"/>
        <context ref="encoding-and-decoding-functions"/>
        <context ref="null-related-functions"/>
        <context ref="environment-and-identifier-functions"/>
        <context ref="aggregate-functions"/>
        <context ref="analytic-functions"/>
        <context ref="object-reference-functions"/>
        <context ref="model-functions"/>
        <context ref="ansi-reserved-words"/>
        <context ref="oracle-reserved-words"/>
        <context ref="sql-statements"/>
        <context ref="operators"/>
        <context ref="conditions"/>
        <context ref="sql-plus-at-sign"/>
        <context ref="sql-plus-double-at-sign"/>
        <context ref="sql-plus-slash"/>
        <context ref="sql-plus-commands"/>
        <context ref="pl-sql-reserved-words"/>
        <context ref="pl-sql-keywords"/>
      </include>

Now, fire up gedit again, open a PL/SQL script…and luxuriate in the glow of sumptuously highlighted keywords…or if you’re feeling picky, try and spot any that I’ve missed.