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.
- SSH key to your database server
- 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
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.
"taskName": "print hello",
"taskName": "Compile in bmc",
"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.