Compile PL/SQL with VS Code using SSH

March 27, 2017 cruepprich

First of all thanks to Morten Braten’s blog post Using VS Code for PL/SQL development. I had been wanting to try this editor for a while but still felt loyal to Sublime.

Morten described how to use Visual Studio Code for PL/SQL development, and how to use its task runner to compile your code via SQL*Plus. I would like to expand on this aspect and show how I used the task runner to compile code through an SSH tunnel via SQLcl. I’m using a Mac, so you may have to do some adjustments for other operating systems.

Basic requirements:

SSH key to your database server
SQLcl
VS Code

First I created an SQL file (bmc_login.sql) (make sure to place it in your SQLPATH) that will log my SQLcl session into my remote database via SSH:

sshtunnel christoph@bmcdb -i /Users/cruepprich/bmc/bmc_key -L 8888:localhost:1521
connect scott/tiger@localhost:8888/orcl
@&1
exit

For more information about how this works see OraOpenSource.

Then I needed a shell script (compile_bmc.sh) that the task runner can execute:

~/oracle/sqlcl/bin/sql -S /nolog @bmc_login $1

This file calls the SQLcl executable in silent mode (-S) without logging in (/nolog), and runs the bmc_login.sql to connect to the remote database. Then it executes the first command line argument ($1) which it will get from the task runner. Finally it will display any compilation errors and exit.

The last thing to do is to configure the task runner in VS Code. Edit the tasks.json file and add the following task. Note that I’m showing an example with multiple tasks. The task of interest is the one named Compile in bmc.

{
    "version": "0.1.0",
    "tasks": [
        {
            "taskName": "print hello",
            "command": "echo",
            "args": ["Hello"],
            "isShellCommand": true
        },
        {
            "taskName": "Compile in bmc",
            "command": "/Users/cruepprich/sh/compile_bmc.sh",
            "isShellCommand": true,
            "args": ["${file}"]
        }
    ]
}

The ${file} argument refers to the file you are currently editing. So you want to make sure that you are editing a PL/SQL program and then you can run this task now from the VS Code Command Pallete:

You can also add a keyboard shortcut (ctrl-c) to run this task.

 {
   "key": "ctrl+c",
   "command": "workbench.action.tasks.runTask",
   "args": "Compile in bmc"
 }

Now I can edit PL/SQL programs in VS Code and compile them directly on my remote database via SSH tunnel.