Tuesday, March 21, 2017

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! :-)


20 comments:

Jorge Rimblas said...

This is awesome! By reformatting the errors I was able to get the errors recognized on Sublime Text. Very cool...
Now to find some time with VC

Unknown said...

Thanks so much for this. Really clear, easy to follow and a great little editor.

I plan to run it alongside atom to do a compare and contrast.

Gussay said...

Thanks for this Morten.
I'd already started using VS Code but hadn't been able to explore this stuff due to being too busy.

Your blog has given me the push start I needed!

Well written, easy to follow steps

Anonymous said...

Thanks for posting this. I've been thinking about trying it out. I love the Node.js debugging and the GitHub integration! Nice!

Ming (Tim) Lei said...

Comparing with Atom, VS Code is a better choice for less RAM consumption and faster response, at least on Win10. Using an old but usable software glass2k to change the transparency will make it nicer.

Etay Gudai - Oracle Israel said...

Just saying SUPER THANKS for all your great articles and time invested. helped me (and I am sure others ) A LOT !.

Anonymous said...

Thanks for this great blog. I'm just facing an error due to blanks in my path.

Anonymous said...

This helped out a l lot. Thank you. However, this outputs ALL database errors, and we have hundreds. I tried to make it only output the errors for the package I was currently compiling but I can't get it to work. I think I'm very close though. This is what I did...

(1) I added "${fileBasenameNoExtension}" to the list of args in the tasks.json file.

(2) I pass that new arg through to the _run_sqlplus.bat like so: echo exit | echo @_show_errors.sql %3 | sqlplus %1 %2

(3) Then I add this to the where clause in _show_errors.sql: and lower(name) like '%'||lower(&1)||'%'

The package is named "test.pkb", so it passes "test" through all those steps. But I get nothing selected. Any idea what I'm missing?

Morten Braten said...

@Anonymous:

> "this outputs ALL database errors, and we have hundreds"

Sounds messy! I prefer a clean dev environment with as few errors as possible...

> "The package is named "test.pkb", so it passes "test" through all those steps. But I get nothing selected. Any idea what I'm missing?"

In step 3, I think you need single quotes before and after the &1 because that gets passed as a literal value to the script? Ie:

and lower(name) like '%' || lower('&1') || '%'



- Morten

Anonymous said...

Big thanks to this blog!

Works very well for me with latest 11g

Anonymous said...

Thanks it's working, but not all errors are appear in problem section. I think there must be some problem with regular expression.

for example this error wasn't appeared.

"error 901/64 xxxxxxxxxxxxxxx.sql PLS-00103: Encountered the symbol "," when expecting one of the following:

. ( * % & = - + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
"

Anonymous said...

Hi,
I have errors in the "OUTPUT" tab, not in "PROBLEMS".
So text is not colorized.
I use run task, not build, because I don't have package.json file.

Can you help me?

Thanks

Anonymous said...

Hi Morten,
Big thanks for converting me to VSC.

Unknown said...

I have the Pivot plug-in installed in my application and the Pivot is working fine.
I need to generate the reports dynamically based on the selection of the Pivot parameters.

Appreciate if you can kindly provide me any assistance related to this.

Morten Braten said...

@Unknown: This blog post is not about the pivot plugin. Please post your comments to the appropriate blog post.

- Morten

Anonymous said...

Hey @Morten Braten,
Thanks for the very detailed desciption of how to use Visual Code for PL/SQL Development.

I have a big problem getting your compiling running. I did everything like you described it, but I will get the following error:

_run_sqlplus.bat : Die Benennung "_run_sqlplus.bat" wurde nicht als Name eines Cmdlet, einer Funktion, einer Skriptdatei oder eines ausführbaren Programms erkannt. Überprüfen Sie die Schreibweise des Namens, oder ob
der Pfad korrekt ist (sofern enthalten), und wiederholen Sie den Vorgang.
In Zeile:1 Zeichen:1
+ _run_sqlplus.bat apps/apps@ebs14srv.intern.promatis.de:1521/EBS14SRV ...
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (_run_sqlplus.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException


Do you have an idea, what I did wrong?

Regards
Yves

Morten Braten said...

@Yves, I've had similar problems when I tried to switch from version "0.1.0" in the tasks.json file to the newer "2.0.0" version. Please check your version and see if that helps.

- Morten

peter said...

Could you please share the config and script for use with the 2.0.0 version?
It works when just using the sqlplus in the bat file , but can't get it to work with the showerrors.

Morten Braten said...

@Peter: Last time I looked into this (some months ago), I couldn't get it to work with the 2.0.0 version, so I switched back to the 0.1.0 version. Haven't had free time to investigate since then.

- Morten

Marco Patzwahl said...

Hi folks,
thanks for sharing your knowledge,
i have also Version 2.0 and changed a little bit of your Code and it runs:

_run_sqlplus.bat:
rem Setup sqlplus environment for Unicode
rem Run this before running scripts that contain multibyte characters
set NLS_LANG=.AL32UTF8
chcp 65001

rem show raw error messages from sqlplus
rem echo exit | echo show errors | sqlplus %1 %2

rem get errors that can be parsed with problemMatcher
cls
echo exit | echo @_show_errors.sql | sqlplus -s %1 %2


task.json
{
"version": "2.0.0",

// Run sqlplus via a batch file
"windows": {
"command": "./_run_sqlplus.bat"
},
"osx": {
"command": "./_run_sqlplus.sh"
},
"linux": {
"command": "./_run_sqlplus.sh"
},

"presentation": {
"reveal": "always", // never,silent,always
"panel": "shared" // new,shared,dedicated
},

// first argument is the database connection string
// second argument is the file to execute (ie the current file)
"args": ["scott/tiger@172.30.30.2:1521/xe.muniqsoft-training.de", "@${file}"],

// use this to get any database errors (from user_errors view) listed in the "Problems" pane in VS Code
"problemMatcher": {
"owner": "plsql",
"fileLocation": ["relative", "${fileDirname}"],
"pattern": [
{
"regexp": "(.*) (\\d*)\/(\\d*) (.*?) (.*)",
"severity": 1,
"line": 2,
"column": 3,
"file": 4,
"message": 5,
"loop": true
}
]
}
}

If you have still problems with the Path, yous a full qualified path like c:\temp\test.sql

Hope this helps!
Marco