PL/SQL (Oracle) for Visual Studio Code

This extension adds support for the PL/SQL (Oracle) to Visual Studio Code.

Colorization

Full syntax highlight for PL/SQL files based on oracle-textmate-bundle

An advanced customization can be done by using an extensionDependencies, follow this exemple

Go to Symbol

Navigate

• to procedures, functions
• to constants, variables, type, subtype, cursor (declared in spec/body part of a package)

Go to Definition

Navigate to methods (procedures and functions) with some limitations :

• Go to a symbol (see Go to Symbol) in the same file
• Go to a symbol (see Go to Symbol) in another file whose name includes the package or method name.
  e.g.: XXX_MyPackage.pkb or XXX_MyFunction.sql

Define search paths

Use the settings plsql-language.searchPaths to specify where to search.

• null (default) search in all workspaces.
• ${workspaceFolder}: limit search in current workspace
• ${workspaceFolder: name}: limit search by name of workspace
• ${workspaceFolder: index}: limit search by index of workspace
• path: limit search in this folder

You can specify multiple locations by using an array.
e.g.: ["${workspaceFolder: pl}", "C:/Develop/MyProjects/Oracle"]

Documentation

Generate detailed documentation automatically for procedures and functions.

Use Ctrl+Space (like others snippets) when the caret is on an empty line, above a function or a procedure declaration, a 'special' snippet is generated. (with prefix __doc by default)

The default template is here.
(don't modify this file, it'll be overwritten with the update of the extension !)

    plsql-language.pldoc.path:   to define your own snippet and specify its location
    plsql-language.pldoc.author: to define the author.
    plsql-language.pldoc.enable: to disabled this feature

To customize:

• Create your own file pldoc.json.
  Don't change the default file because it'll be overwritten the next time you update this extension
• Define the path (folder only) to your custom file by using the setting plsql-language.pldoc.path

Note
If documentation begins with /** (double stars), it'll appear in Signature, Hover, Completion.

Completion

There is intelliSense for package members (autocompletion from package files).

You can also define your own completion for tables/fields. An exemple is here.
(don't use this file, it'll be overwritten with the update of the extension !)

    plsql-language.completion.path: to specify its location

Snippets

Some snippets available

    plsql-language.snippets.enable: to disabled snippets defined in this extension

Note

For this extension works with .sql files you must change your settings (user or workspace) like this:

    "files.associations": {
       	"*.sql": "plsql"
    }

Connection

You can configure a list of connection in settings and use the active one in your tasks (see below). Use the command: PLSQL - Activate connection or click on status bar.

The oracle db connection feature is still under constuction, this is a preview version. To activate it plsql-language.oracleConnection.enable: true (false by default)

You can run the current SQL statement (CTRL+ENTER). If no selection, run current command delimited by character /
Warning:

• Don't use or select character ; on a select statement.
• Don't select character /

You can also run an SQL statement from another extension like this:

    // To access db from another extension
    // Use active connection
    try {
         const result = await vscode.commands.executeCommand('plsql.executeCommand', {sql: 'SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = :NAME', params: ['John']})
         vscode.window.showInformationMessage(JSON.stringify(result));
    } catch(err) {
         vscode.window.showErrorMessage(JSON.stringify(err));
    }

    // Or create a specific connection
    try {
        // with connection params
        // let _connection = await vscode.commands.executeCommand('plsql.createConnection', {user: 'hr', password: 'hr', connectString: 'localhost:1521/xe' })
        // or with a tag to find in setting of connections
        let _connection = await vscode.commands.executeCommand('plsql.createConnection', {tag: 'hr'})
        const result = await vscode.commands.executeCommand('plsql.executeCommand', {sql: 'SELECT LAST_NAME FROM EMPLOYEES', connection: _connection})
        vscode.window.showInformationMessage(JSON.stringify(result));
        await vscode.commands.executeCommand('plsql.removeConnection', {connection: _connection});
        _connection = null;
    } catch(err) {
        vscode.window.showErrorMessage(JSON.stringify(err));
    }

Prerequiste:

• Install Node.js from nodejs.org

Notes:

• node-oracledb is used externally to use pre-built node-oracledb binary
• npm install oracledb is automatically executed on the first activation (when using connection) and installed here: .../.vscode/extensions/xyz.plsql-language-_version_\server-oracle\node_modules\oracledb
• Please consult node-oracledb site to resolve issues when installation failed.
• vscode extensions don't supports binary node module. (This is the reason why command npm install oracledb is executed.)

Roadmap:

• List of connections in settings.
• Complete fields list in settings (connect as...)
• Connect to DB
• Execute SQL
• Run file as a script
• Auto-complete

Compile / Task

You can compile a PLSQL package with sqlplus, create a task like this:

    {
            "version": "2.0.0",
            "tasks": [{
                    "label": "sqlplus",
                    // Run sqlplus
                    "command": "sqlplus",
                    // Alternative (see below)
                    // "command": "run_sqlplus.bat",

                    "args": ["username/password@sid", "@\"${file}\""]
                    // Alternative: use active connection defined in settings
                    // "args": [${config:plsql-language.connection.activeInfos}, "@\"${file}\""]
            }]
    }

To force sqlplus to complete, it is better to use a batch file like this:

    run_sqlplus.bat
            echo exit | echo show errors | sqlplus %1 %2

This will run sqlplus, output any errors, and then exit cleanly back to VS Code.
Thanks to @mortenbra (issue #5)