Oracle Developer Tools for VS Code

  • Arthur
  • IT
(Last Updated On: 2021-03-15)

Welcome! Oracle Developer Tools for VS Code enables Visual Studio Code developers to connect to Oracle Database and Oracle Autonomous Database, edit SQL and PL/SQL with autocomplete, intellisense, syntax coloring, and code snippets, execute SQL and PL/SQL and view and save the results in formats such as .CSV and JSON. The Oracle Database Explorer tree control allows you to quickly explore your database schema, view table data, and edit, execute and save PL/SQL.

This Quick Start will help you install, connect and configure Oracle Developer Tools for VS Code.

Note: This document assumes you are using version 19.3.2 (released 9/30/2019). Follow the instructions below to update if you are using an older version. We plan to have frequent releases.

Known Issue:

ORA-12154 when reconnect from Oracle Database Explorer after upgrade to 19.3.2: A Oracle Explorer database connection created with 19.3.1 will give a ORA-12154 TNS:could not resolve the connect identifier specified error after upgrading to 19.3.2. To resolve, right click on the connection and select Update Connection. Then press the Update Connection button in the connection dialog.

Quick Start

Install

  • Install .NET Core Runtime version 2.1 or later:
  • Click on the Extensions icon in the Activity Bar on the side of VS Code or use the View: Show Extensions command (Ctrl+Shift+X)
  • Type Oracle in the extension search bar
  • Locate Oracle Developer Tools for VS Code and click Install
  • When the installation is complete, restart Visual Studio Code

Update this Extension

  • Visual Studio Code automatically checks for extension updates and installs them. After updating you will be prompted to restart Visual Studio Code.
  • If you have disabled automatic updates, press F1 to open the Command Palette and enter the Check for Extension Updatescommand and click the Update… button which will appear in the list of installed extensions. For more information click here.

Explore Database Schema with Oracle Database Explorer

  • To view Database Explorer, click the database icon in the Activity Bar on the far left side of Visual Studio Code.
    • Connect: Click the plus sign button to create a new connection, following the steps below to Connect to Oracle Database or Connect using a TNSNAMES.ORA Connection Alias. Once connection nodes display, you can click on the node to view the database schema.
    • Delete Connection: Right click on any connection node and select Delete Connection
    • Update Connection info: Right click on any connection node and select Update Connection
    • Disconnect: Right click on any connection node and select Disconnect
    • View and Save Table/View Data: On Table and View nodes menu, select Show Data. For more information see View and Save Result Sets below.
    • Edit PL/SQL: On stored procedures, functions, packages, or trigger nodes select Edit/Edit Package Body/Edit Specification to open PL/SQL into a file for editing. See Edit Existing PL/SQL in your Database below for more details.
    • Save PL/SQL to database: Right click in the PL/SQL code and select Save to Database
    • Save PL/SQL to file: Select the tab with the PL/SQL code in it. In Visual Studio Code menu select File->Save As
    • Execute PL/SQL: From menu, select Run to execute a stored procedure or function
    • Refresh: Right click on any node and select Refresh to refresh that node and all child nodes
    • Filter: After expanding a long list of nodes (for example, all relational tables) you can filter them by mousing over any part of Oracle Database Explorer and typing a string, then press return. This will highlight all matches for that string. You can then click on the hamburger icon to only show matches in Oracle Database Explorer.

Connect to Oracle Database

  • (If your database is in the Oracle Cloud go to Connect to Oracle Autonomous Database below)
  • To connect to Oracle Database from a .SQL or .PL/SQL file, press F1 to open Command Palette and select Oracle:Connectfrom the dropdown
  • To connect from Oracle Database Explorer, click the plus sign button
  • A connection dialog will open. In the Connection Type dropdown, select Basic
  • Enter the database hostname, port number and service name
  • Select the database role from the Role drop down list
  • Enter the username and password
  • Provide a connection name to be used to reference this connection in Database Explorer and elsewhere
  • Click the Create Connection button

Connect using a TNSNAMES.ORA Connection Alias

  • Copy the TNSNAMES.ORA file that you want to use into the directory that is set as “Config Files Location” in the Oracle Developer Tools for VS Code Extension Settings, or change this setting as desired. By default this location is%USERPROFILE%\Oracle\network\admin on Windows, and ~/Oracle/network/admin on Linux and Mac.
  • If you don’t have a TNSNAMES.ORA but would like to create one, see the example located in%USERPROFILE%\.vscode\extensions\oracle.oracledevtools-19.3.2\sample\network on Windows and in~/.vscode/extensions/oracle.oracledevtools-19.3.2/sample/network on Linux and Mac.
  • To connect to Oracle Database from a .SQL or .PL/SQL file, press F1 to open Command Palette and select Oracle:Connectfrom the dropdown
  • To connect from Oracle Database Explorer, click the plus sign button
  • A connection dialog will open. In the Connection Type dropdown, select TNS Alias
  • Make sure the TNS Admin Location field is set to the directory where your TNSNAMES.ORA file is located. If not, change it.
  • Select an alias from the TNS Alias dropdown list
  • Select the database role from the Role drop down list
  • Enter the username and password
  • Provide a connection name to be used to reference this connection in Database Explorer and elsewhere
  • Click the Create Connection button

Connect to Oracle Autonomous Database

  • Obtain your Client Credentials File: Go to the Administration Console for your Oracle Autonomous Database and clickDownload Client Credentials.  Alternatively, obtain the credentials file from your administrator.
  • Unzip the credentials files into the directory that is set as “Config Files Location” in the Oracle Developer Tools for VS Code Extension Settings. By default this location is %USERPROFILE%\Oracle\network\admin on Windows, and~/Oracle/network/admin on Linux and Mac.
  • To connect to Oracle Database from a .SQL or .PL/SQL file, press F1 to open Command Palette and select Oracle:Connectfrom the dropdown
  • To connect from Oracle Database Explorer, click the plus sign button
  • A connection dialog will open. In the Connection Type dropdown, select TNS Alias
  • Make sure the TNS Admin Location field is set to the directory where you unzipped your credentials files. If not, change it.
  • Select the alias name you wish to connect to, for example mydb_high, from the TNS Alias dropdown list. (Note that if there are multiple Autonomous Database instances in your cloud account, you will see aliases for all instances)
  • Select Non-Administrator from the Role drop down list
  • Enter the username and password. (If you are new to Oracle Autonomous Database, use username admin)
  • Provide a connection name to be used to reference this connection in Database Explorer and elsewhere
  • Click the Create Connection button

Connect Using OS Authentication

  • If using both Windows Client and Windows Database Server, set SQLNET.AUTHENTICATION_SERVICES=(NTS) in a SQLNET.ORA file located in the directory that is set as “Config Files Location” in the Oracle Developer Tools for VS Code Extension Settings. By default this location is %USERPROFILE%\Oracle\network\admin
  • Follow the steps in Connect to Oracle Database or Connect using a TNSNAMES.ORA Connection Alias above, except
  • When prompted for a user id enter “/” (without the quotes).

Connect Using a Proxy User

  • To connect to Oracle Database from a .SQL or .PL/SQL file, press F1 to open Command Palette and select Oracle:Connectfrom the dropdown
  • To connect from Oracle Database Explorer, click the plus sign button
  • A connection dialog will open. In the Connection Type dropdown, select ODP.NET Connect String
  • In the Connection String field, enter a string with this format if providing the hostname, port and service name explicitly: User Id=myuser;Data Source=dbhostname:1521/DBSERVICENAME;Proxy User Id=myproxyuser;Proxy Password=myproxypass;
  • If using a TNS Alias, enter a connect string with this format: User Id=myuser;Data Source=mytnsalias;Proxy User Id=myproxyuser;Proxy Password=myproxypass;
  • Provide a connection name to be used to reference this connection in Database Explorer and elsewhere
  • Click the Create Connection button

Edit Existing PL/SQL in your Database

  • View Database Explorer by clicking the database icon in the Activity Bar on the far left side of Visual Studio Code.
  • If the Database Explorer pane is empty (no connection nodes showing), click the plus sign button to create a new connection, following the steps above to Connect to Oracle Database or Connect using a TNSNAMES.ORA Connection Alias. Once connection nodes display, you can click on the node to view the database schema.
  • Navigate in the tree control to the PL/SQL package, stored procedure/function, or trigger you want to edit.
  • Right click on the PL/SQL package, stored procedure/function, or trigger and select Edit/Edit Package Body/Edit Specification to open PL/SQL into a file for editing.
  • When done editing, right click in the PL/SQL code and select Save to Database

Open Existing .SQL or .PL/SQL File

  • (Note:You can save PL/SQL in your database to a file by going to Oracle Database Explorer, opening a package, procedure, function, or trigger in the Explorer and then in Visual Studio Code menu select File->Save As)
  • In VS Code menu select File->Open File or press (Ctrl-O)
  • Select the file you wish to open
  • Press F1 to open Command Palette and select Oracle:Connect from the dropdown
  • If prompted, select Oracle-SQL and PLSQL from the languages dropdown.
  • Select an existing connection profile from the list or create a new one using the steps in Connect to Oracle Database orConnect using a TNSNAMES.ORA Connection Alias above.

Create a New .SQL or .PLSQL File

  • Press F1 to open Command Palette and select Oracle:Develop New Query from the dropdown
  • Select an existing connection profile from the list or create a new one using the steps in Connect to Oracle Database orConnect using a TNSNAMES.ORA Connection Alias above.
  • When done editing, in the VS Code menu select File->Save As to save the file.

Execute SQL and PL/SQL

  • Type some SQL or PL/SQL you wish to execute into the .SQL or .PLSQL file
  • While typing, autocompletion suggestions will appear as you type, for example column names. You can click the i icon in the suggestions to get more details such as the schema objects the suggestion is associated with. Use the arrow keys to navigate through the suggestions, and the enter key to select a suggestion.
  • Type a schema name followed by a period, for example HR. to use intellisense to view and select database objects.
  • While typing, some code snippet suggestions may appear. You can view more snippets by typing oracle on a new line.
  • Position the cursor on a line that contains the SQL or PL/SQL that you wish to execute. Alternately, you can select (highlight) one or more SQL or PL/SQL statements. Right click and select Execute SQL from the menu. Select Execute All if you wish to execute all SQL and PL/SQL in the current file.

View and Save Result Sets

  • If your SQL execution returns rows, a new document will open with the result set. The number of rows initially fetched is controlled by the Data Batchsize extension setting. As you scroll through the rows, more will be fetched.
  • To save results, choose the format: .CSV or JSON. Then click the icon to Save Selected Rows or Save All Rows. After providing a file name and location, the file will open in Visual Studio Code.
  • To copy the selected rows to the clipboard, click the Copy Selected Row(s) to Clipboard icon

Use a Horizontal Editor Layout

  • To modify the default window layout of Visual Studio Code to split the screen horizontally, for example to have a .SQL file in an upper pane and the SQL execution results in a lower pane, do the following:
  • In the VS Code menu, select View->Editor Layout->Two Rows.
  • Execute some SQL or PL/SQL as described above in Execute SQL and PL/SQL
  • Drag the SQL results tab into the lower pane.
  • Any additional SQL executions will continue to be displayed in this tab in the lower pane until it is closed.

Create or Modify Keyboard Shortcuts

  • To create keyboard shortcuts (keybindings) or to modify existing ones, go to the File menu in VS Code and selectPreferences->Keyboard Shortcuts
  • In the search text box, enter a portion of the name of the extension whose shortcut you would like to modify, for example “oracle”.
  • A list of commands and their shortcuts will appear. Click the pencil icon on the left side of a row to edit a shortcut, or + (plus sign) to create a new one.
  • When prompted, enter the key combination and if it is not being used by another extension, press enter. If it is being used by another extension, press escape and try again.
  • To delete a shortcut, right click on the shortcut and select Remove Keybinding

Get Help or Provide Feedback