Compile PL/SQL with VS Code using SSH

March 27, 2017 at 8:04 am | Posted in Oracle Developement | Leave a comment

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

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 <

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:
command Pallette
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.

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Entries and comments feeds.

%d bloggers like this: