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.
The versions I have used are shown in brackets
- Microsoft VS Code (1.35.1)
- Oracle Database ( XE 220.127.116.11.0)
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:
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
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:
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 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.