Using VS Code for PL/SQL development

I've been using Sublime Text as my main editor for PL/SQL development for many years, but I'm now in the process of switching to Visual Studio Code (VS Code).

Some good reasons to use VS Code:

• Multi-platform (Windows, OS X, Linux)
• Free, open source
• Lightweight, fast (enough)
• Large ecosystem of extensions
• Built-in Git support
• Can be adapted to PL/SQL coding via a plsql language extension (syntax highlighting, go to/peek definition, go to symbol) and PL/SQL compilation using sqlplus via a Task Runner (see below for PL/SQL specifics)

Installing VS Code and extensions

Download VS Code from https://code.visualstudio.com/ and run the installer.

Start VS Code and click the Extensions icon.

• Search for "plsql" and install the "xyz.plsql-language" extension

 The following are all optional but recommended:

• Search for "git history" and install the "donjayamanne.githistory" extension
• Search for "better merge" and install the "pprice.better-merge" extension (update 24.09.2017: this extension is now part of VS Code itself and does not need to be installed separately)
  
• Search for "git lens" and install the "eamodio.gitlens" extension
• Search for "tag" and install the "formulahendry.auto-close-tag" extension
• Search for "blackboard" and install the "gerane.Theme-Blackboard" extension
• Search for "material icon" and install the "PKief.material-icon-theme" extension
• Search for "plsql" and install the "apng.orclapex-autocomplete" extension (added 02.01.2018)

Click on "Reload" to restart VS Code with the new extensions loaded.

Your list of extensions should now look something like this:

Click File, Preferences, Color Theme and select the "Blackboard" theme.
Click File, Preferences, File Icon Theme and select the "Material Icon" theme.

Configuring a Task Runner to compile PL/SQL code

Click File, Open Folder and open a folder containing your PL/SQL code.
Click View, Command Palette and enter "task" then select "Configure Task Runner", select "Others".

Copy the following text and paste it into the tasks.json file:



Adjust the connection string as appropriate to your environment.

Copy the following text and save it as _show_errors.sql in the project root folder:



Linux and Mac: Copy the following text and save it as _run_sqlplus.sh in the project root folder (remember to chmod +x the file to make it executable).



Windows: Copy the following text and save it as _run_sqlplus.bat in the project root folder.



Optionally create a login.sql file in project root folder and add:



Editing code

The PL/SQL language extension by xyz provides syntax highlighting for PL/SQL, as well as a couple of very useful code navigation features.

You can go to a "symbol" (ie a function or procedure) inside a package by pressing Shift+Ctrl+O and typing the name of the symbol:

You can go to the definition of a function or procedure by pressing F12 when the cursor is on the function or procedure name (or right-click on the function or procedure name and select either "Go to definition", or "Peek definition" to see the definition in a popup window without leaving the current file).

Snippets

You can define your own snippets for frequently used code; see this how-to article. I recommend that you create snippets for frequently used code blocks such as if/then/else statements, case statements, and larger code blocks such as the skeleton for a package, procedure or function.

Building code

To build (compile) the current file into the database, press Shift+Ctrl+B (or click View, Command Palette, and type "build" to search for the relevant command).

The build task runs sqlplus and passes it the filename of the current file. This creates or replaces the object in the database (you can see the commands being executed in the "Output" pane in VS Code). The build task then queries user_errors to get any errors and warnings from the database, and these are shown in the "Problems" pane in VS Code.

You can click on each problem to jump to the relevant line of code. You'll also see squiggly lines under the errors in the code itself, and you can hover over the text to see a tooltip containing the error message.

Note that since the build task queries the user_errors view without any filters, you get to see all errors in the schema, not just the errors for the current file. I actually like this, because it instantly shows me if there are any other problems in the schema that I might not otherwise be aware of.

Also, the build task assumes that you have one file per database object (ie separate files for package specifications and package bodies), and that the filenames match the database object names. This makes sense to me and I assume that's how most people organize their files, but you can tweak the _show_errors.sql script to generate output appropriate to your setup.


Version control with Git

VS Code has great built-in support for Git. I recommend installing a couple of git-related extensions (see above), but other than that, I'll just refer you to the official tutorial for using Git in VS Code.


That's it, enjoy using VS Code for PL/SQL development! :-)