Pretty PL/SQL

When presenting code in a web page, one of the challenges is to get it looking attractive and readable.

Perhaps this isn't the main challenge. Having something useful to say, adding enough detail to illustrate the point but not so much that it obscures it, testing it and so on should perhaps be higher priorities - but it's a fact of life that a good-looking document will make your point better than a messy one. 

create or replace function testfun
   ( p_someparam boolean default true )
   return number
as
begin
   do_something(p_someparam);
   return case when p_someparam then 1 else 2 end case;
end;
  
create or replace function testfun
   ( p_someparam boolean default true )
   return number
as
begin
   do_something(p_someparam);
   return case when p_someparam then 1 else 2 end case;
end;

There are several script libraries for syntax-highlighting code examples on the web. For example:

Strangely, although all of these support multiple languages (SyntaxHighlighter supports 130), and Oracle is the second largest software company in the world, with PL/SQL #16 on the TIOBE programming popularity index as of 2020, I couldn't find a single online syntax highlighter that supports PL/SQL.

(March 2016: Adrian Billington pointed out that SHJS, which he uses on oracle-developer.net, has an Oracle SQL module covering most of the Oracle and PL/SQL keywords as of around 1994, and it's pretty easy to add missing ones as you need them. However it's still just a keyword list, it doesn't support q-quotes, and - as far as I can tell from looking at the code - it thinks double-quotes indicate a text string.)

(June 2020: Prism lists PL/SQL as a supported language. Good to see!)

(September 2020: Stack Exchange switched from Google Code Prettifier to highlight.js.)

Even Oracle's own OTN Forums (powered by Jive) can only manage a generic "SQL" which doesn't recognise any procedural elements such as if or loop, let alone anything specific to Oracle SQL like package or type, and certainly nothing from PL/SQL such as exception or pragma.

Even when a SQL highlighter does more-or-less cover PL/SQL by hitting the main keywords, the word-matching approach is oblivious of context, so for example create or replace package annoyingly has the words "or" and "replace" in different colours because or is an operator and replace is a function. (The same thing happens to bulk collect, nulls first, pipe row etc). There also seems to be a widespread error in which the "\" character is allowed to escape a quote, so a quoted DOS path such as 'c:\data\files\' breaks your syntax highlighting despite being perfectly valid code. They also tend to treat double-quoted expressions as text strings, when clearly they are nothing of the sort.

Rolling your own

I downloaded the Google Prettyprinter Javascript library and CSS file, and made my own copy of lang-sql.js as lang-plsql.js. I copied all keyword and function names straight from the Oracle 12c documentation - a bit of a task as not everything is in one place, and I didn't just want to import the whole of v$reserved_words as there are a lot of terms in it that aren't really relevant, and in any case I wanted to separate keywords from functions etc.

Javascript regular expressions will be familiar to anyone who's used the Oracle regexp_ functions. My "keyword" list now includes bulk\s+collect, pipe\s+row and or\s+replace, so these words are only treated as keywords when they're part of the full construct, and collect for example will still be highlighted as a function when it appears without bulk before it. Variables beginning with & or : cover binds and SQL*Plus substitution variables. I got some help for q-quotes (the q'[...]' syntax) from Stack Overflow.

I found as I tested code examples that the predefined exceptions (no_data_found etc) are worth highlighting. For example:

exception when no_data_found or access_into_null or too_many_rows then

This required a new "exc" class in addition to the existing kwd, str, typ etc. (Ada seems to be the only other language that has named exceptions. Java and others have exception classes like IOException, but they are still classes, and code-prettify just treats them as regular types.)

Using Github

After getting what was essentially a hacked version working, I decided to do it properly and set up a GitHub account, installed Github Desktop, forked PrettyPrint and created my own build. When I have some neatly packaged test cases I may submit a pull request, and then one day maybe (we can dream) PL/SQL highlighting will be an option on Stack Exchange. (Update 2020-09: Stack Overflow is migrating to highlight.js, so this isn't going to happen.)

Building is pretty straightforward. You edit the verbose, commented versions of lang-plsql.js etc, then the build process generates compact versions and downloadable zipfiles. To build code-prettify, you just need to open a command line in the code-prettify directory and type make. The only setup I needed on my Mac was adding the following line to my .profile, as the build needs a command-line JavaScript interpreter, and fortunately Macs already have jsc (for Windows you could look at this Stack Overflow thread):

export JS_INTERPRETER="/System/Library/Frameworks/JavaScriptCore.framework/Versions/A/Resources/jsc"

How to use it

  1. Download the Code Prettifier from Github.
  2. Copy prettify.js, lang-plsql.js and clearlight.css (theme with light background) or caramelised.css (dark background) into appropriate locations on your site. (You could also use the generic prettify.css, but mine includes an exc element for named exceptions, and provides a lineprinter-style stripe background to accompany line numbering. I was a graphic designer in a former life.)
  3. Add the following to the header of each webpage that needs prettifying (adjusting directory paths as needed): 
    <link href="/css/clearlight.css" rel="stylesheet">
<script src="/js/prettify.js"></script>
<script src="/js/lang-plsql.js"></script>
<script> document.addEventListener("DOMContentLoaded", function(event) { prettyPrint(); }); </script>

    (Or, instead of the document.addEventListener() line, you could use an "onload" call in the page's body element. This avoids the need for an IE8 workaround too tedious to mention here, but clutters up your HTML a little.)
  4. Add a class tag as below to each <pre> or <code> element that needs prettifying. 
    <pre class="prettyprint lang-plsql">
    select distinct synonym_name || '.' || procedure_name
from   all_procedures p, all_synonyms s
where  p.owner = 'SYS'
and    s.owner = 'PUBLIC'
and    s.table_name = p.object_name
and    p.object_name != 'STANDARD'
and    p.object_name not like '%$%'
and    p.procedure_name not like ' %'

    Use <span class="nocode"> to mark sections you don't want prettified, such as SQL results. (A current limitation is that this won't work with the linenums option.) 

    select distinct synonym_name || '.' || procedure_name
from   all_procedures p, all_synonyms s
where  p.owner = 'SYS'
and    s.owner = 'PUBLIC'
and    s.table_name = p.object_name
and    p.object_name != 'STANDARD'
and    p.object_name not like '%$%'
and    p.procedure_name not like ' %'


PROCEDURE_NAME
------------------------------
RESULTS.EXAMPLE

1 row(s) selected.

    Add the linenums class if you want line numbers.

    <pre class="prettyprint linenums lang-plsql">
    select distinct synonym_name || '.' || procedure_name
from   all_procedures p, all_synonyms s
where  p.owner = 'SYS'
and    s.owner = 'PUBLIC'
and    s.table_name = p.object_name
and    p.object_name != 'STANDARD'
and    p.object_name not like '%$%'
and    p.procedure_name not like ' %'

Reading text files into an HTML page

The next challenge was getting code into a web page. Small code examples like the ones above are all very well, but sometimes the purpose of the document is to introduce a script or something more substantial like a PL/SQL Sudoku solver, and I want to maintain the code separately from the document.

If your web server supports it, the easiest and most efficient way is to use a server-side include. Place something like this in your document where you'd like the text file to appear, and Apache will include its contents before serving the document to the browser: 

<!--#include virtual="/code/somefile.sql" -->

Another way to do this is using JavaScript's built-in XMLHttpRequest. I found this construction in several sites, so I copied it and added an optional call to PrettyPrint.

To use it:

  1. Copy textfile-reader.js to the JavaScript directory on your site.
  2. Add the following to the document's HTML header: 
    <script type="text/javascript" src="/js/textfile-reader.js"></script>
  3. Extend the <body> tag with the following onload call, referencing the file you want to load: 
    <body onload="readFile('/path/somefile.sql', 'filehere');">
  4. At the point in your document where you want the other file to be inserted, add this (I've used "filehere" but it can be any label): 
    <pre id="filehere"></pre>

The readFile() function takes an optional third parameter, "prettify", which defaults to true. Set to false if you don't want the text prettified.

Here's an example. The following is just a <pre> block like the example above, with the external file specified as /code/dict.sql: 

    <pre id="filehere">[[Textfile failed to load]]</pre>

[[Textfile failed to load]]

Voilà - this HTML document now dynamically shows the contents of the external text file /code/dict.sql, complete with syntax highlighting.

Next steps

If I can, I'd like to: