This post shows you how to configure the Oracle Developer Tools extension for VS Code and then use it to query an existing table, create or alter database objects and execute PL/SQL.

Prerequisites

The versions I have used are shown in brackets

• Microsoft VS Code (1.35.1)
• Oracle Database ( XE 18.4.0.0.0)

Installation

Start VS Code and navigate to extensions then search for Oracle Developer Tools. At the time of writing the home page for this extension was:

Select install.

Configuration

There are several methods of connecting to the database from VS Code, in this guide I will be using the tnsnames.ora approach.

Check where Oracle Developer Tools extension looks for the tnsnames.ora file by:

• Selecting the Oracle Developer Tools extension in VS Code and selecting the manage icon.
• Select Configure Extension Settings
• Look for Config Files Location

• If required amend the location to point to where the tnsnames.ora file is located.
• Restart VS Code.

Connecting to the Oracle Database

Create a new file and from the lower status bar click Plain Text and then select Oracle-SQL and PLSQL (oraclesql).

Press F1 to open Command Palette and select Oracle:Connect from the drop down

Select New Connection using TNSNAMES.ORA

Select the alias name you wish to connect to

Select Non-Administrator

Enter user id and then press return. I connected as the HR user.

Enter password and then press return

Save connection info if desired

Provide profile name or press return for default name

Once you have successfully connected the VS Code status bar will show pertinent information:

Querying

Construct the SQL statement you want to execute. Using the schema name followed by a period to utilise intellisense to view and select available database objects.

To execute the statement you can use the CTRL + E keyboard shortcut or select the statement, right click and choose Execute Query. The results are displayed in a new tab.

Create or alter database objects

Construct the statement required; CREATE TABLE, CREATE PACKAGE, ALTER USER…

Execute the statement using the same method used for querying. Once the statement has completed, the results are displayed in a new tab.

Executing PL/SQL

Executing PL/SQL is identical to the methods above; construct the statement and use CTRL + E to execute.

A limitation at the time of writing is that I have not yet found a way to inspect output from a call to dbms_output.put_line. So whilst I can see that the anonymous block has executed successfully, the expected output of “Hello World” is not shown.

begin
  dbms_output.put_line('Hello World');
end;

I will update this guide once I discover this feature or it becomes available.

Acknowledgements

Quickstart:Get Started with Oracle and VS Code