The following is information related to using Sublime Text for developing Oracle APEX Applications.  Well, actually it is more for any PL/SQL development, but it just sounds cooler.

I use Sublime Text every day and have found it to be a great productivity tool.  As it is cross platform, I use it on MAC, Windows, and Linux operating system (often simultaneously). The pictures may have steps or details specific to one OS or the other. Regardless, the concepts apply to Linux, Mac or Windows. As with many things, there is more than one way to accomplish a goal. This is especially true with Sublime Text. My intention not to re-do Sublime documentation or duplicate what others have already done, but to show you what has worked for me.

Initial Setup

Sublime Text is a commercial software that has a fully functional trial. To the best of my knowledge the trial does not expire, but I like the idea of being able to exercise the software fully before purchasing. (I am a licensed user to support their good work)

Sublime Text

Install Package Control

One of the exciting parts of Sublime Text is not just the multi cursors, but the ability for people to Extend the platform. The folks at Wbond have a package manager that makes extensions very simple.

Package Control Installation

Package Install

After the Package Control is installed, open the use Package Control Command List and install the following packages:

  • Abacus
  • Oracle PL SQL

These two extension packages are what I like to use most to complement the built in functionality of Sublime Text to develop Oracle packages.

Tutorials and References

Some of my favorite tutorial and document sites: Video Tuts Theme Editor (Chrome Only) Tip Sheet Docs and Keybard Shortcuts

Abacus

Abacus is useful for aligning multiple values that follow a similar syntax such as a := ‘value’; The issue for oracle developers is that the := and the => are not supported by default. This is easily fixed.

Navigate to Preferences -> Package Settings -> Abacus -> Settings - Default As a good practice, don’t change the default settings file.  Instead, do a “save as” into the “User Directory” with the same file name.

Abacus Settings

Abacus Save As

Add additional “token” values to the USER settings for Abacus.  Here are the ones that I use.

        {
            "token":                ":=",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                "=>",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                " OUT",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                " IN",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                "||",
            "gravity":              "right",
            "preserve_indentation": true
        },
        // end my additions

Now when using Abacus, it will align the :=, =>, OUT, IN and || tokens in a line.  To use Abacus, you select the lines you would like to align using your mouse or keyboard, then select abacus from the Selection menu or use the shortcut key assigned.

Starting Abacus Align
Before Alignment
After Alignment
Before Alignment
After Alignment

Reference

Oracle PL SQL Package

The Oracle PL SQL extension provides the addition of PL/SQL syntax highlighting in Sublime Text beyond the standard included SQL highlighter. This allows for easier to read code and syntax identification.

For our projects, we use the extensions of .pks and .pkb to identify PL/SQL code. It is easy enough to default the Syntax for the file type using built in Sublime Text features. Use the menu View -> Syntax -> Open All with Current Extension As… to select the default when you have one of the files open.

Default Extension

Reference

Building Code

I subscribe to the adage that Application Express code should be in packages and complex queries exposed via Views. When using Sublime to write that code, I wanted an easy way to compile that code into the database. I researched available packages and did find one, but it was more complex than I desired. (Probably because I don’t know Python that well) I decided to write my own build process that was easier for me to understand and debug. The basis of my Oracle Compilation approach is the same as an installation script. The code I write these days need to be installed via command prompt SQL*PLUS scripting, so this lines up with my goals nicely.

The build process consists of two parts 1 - Sublime Text build option that calls a script command to do the build 2 - A wrapper script that does any work to connect to the database with SQL*Plus and run the desired file

It is necessary to customize as OS environments will handle terminal prompts differently. This is a known behavior. (Windows is different than Mac… so it is said)

SQL*Plus Script

My direction is to have a “script” directory that is in my path for my little tools. This allows the “build shell script” to be called from any directory on that machine. This would apply to any operating system. I place the build script into the accessible path location. Now Sublime Text can open a “shell” and utilize that script.

Build Option

Sublime allows you to write any type of build command you desire. There are substitutions available for passing values to a command. In my case, the command is a “build script”.  From there, one can do any additional work that may be required inside the script with the single command.

To make a new build system available, you simply need to put a *.sublime-build file into your User directory. (see Browse Packages) Once a valid build file has been placed in the directory, it should be immediately available using the name of the file as the Build option.

Sublime Build Options

Selecting a build option will now allow the quick key of Ctrl+B to automatically send the active file to the build script. There is an option to “Save All on Build” that can be turned off if desired.

Note: The build file does have the user name and password embedded in it for convenience and speed. This is a known and intended for development only.

I have included here the scripts that I use for both MAC and Windows. They have different syntax as the environments are different. Example: %1 vs $1 for variables. Place the respective Mac or Windows .sublime_build file in your Sublime Text Packages/Users directory. Place the respective Shell script into a directory in your path statement.

Duplicate the .sublime_build file for each connection making updates to the title and the contents appropriately to reflect it’s connection details.

Sublime Text Oracle Build Scripts - Obsolete link

The build process produces a results window that has the output of the script with a simple keystroke. (Ctrl+B)

Build Results

Any errors that are encountered are identified by the standard SQL*Plus error reporting process.

Build Results Error

Sublime Text can work very well for building DDL scripts.  I do not recommend it for query building or testing as the formatting is SQL*Plus text based. SQL Developer is a much better choice for developing queries and testing results.  Improved productivity can come about by knowing what the right tool for the job is, and how to use it.

Side Note - Linking SQL Developer to Sublime Text

If you are interested in using Sublime Text with SQL Developer - Take a look at the post by Jeff Smith that outlines now to setup external editors inside of the SQL Developer tool.