Oracle Developer Tools for VS Code (SQL and PLSQL)

This extension enables editing and execution of SQL and PL/SQL for Oracle Database and Oracle Autonomous Database.

Version 21.6.0

View the Quickstart: Get Started with Oracle and VSCode for details on how to configure, connect and use Oracle Developer Tools for VS Code. Questions or feedback about this extension? Please post your questions, comments, or report issues on the Oracle Developer Tools forum.

Features include:

• Connect to Oracle Database and Oracle Autonomous Database using hostname/port/service name, aliases in a TNSNAMES.ORA file, connection strings, and more. Choose a default connection to make it easier when opening multiple SQL files. Organize connections by folder, workspace or user scopes.
• Create and manage Oracle Autonomous Databases (ADBs) using Oracle Cloud Infrastructure Explorer tree control. Create, start, stop and terminate ADB instances. Automatically create a walletless database connection or download credentials files.
• Oracle Database Explorer tree control: connect and explore Oracle schemas; show data for tables and views and save as CSV or JSON files; auto-generate CREATE, SELECT, INSERT, and DELETE statements for tables; view, edit, debug and save PL/SQL packages, procedures and functions. Run stored procedures and functions with a UI for entering parameter values. Use filters to limit the schema objects shown and to limit IntelliSense suggestions.
• Edit SQL and PL/SQL with support for hovering, Go to/Peek Definition, Go to/Peek Type Definition, Go to/Peek Implementation, and Go to/Peek/Find All References. The editor also features IntelliSense for autocompletion of schema object names, procedure/function parameters, and SQL*Plus commands. Navigate through large scripts using breadcrumbs. Format SQL and PL/SQL with a configurable formatter.
• Debug PL/SQL in stored procedures, functions, and packages using Visual Studio Code's native debugging features. Compile PL/SQL for debugging, step into PL/SQL, and run to a breakpoint. Enable External Application Debugging to listen for and debug PL/SQL procedures and functions called by applications or SQL scripts.
• Execute SQL and PL/SQL and view and save results. View errors in the Problems panel and navigate to the line with the error. Enter bind and substitution variables into a dialog.
• Explain Plan and Execution Plan
• Limited SQL*Plus support
• View SQL command history and view and save SQL bookmarks

Feature Highlights

Connection Dialog

Connect using hostname/port/service name, aliases in a TNSNAMES.ORA file, Easy Connect connection string, connect descriptor, directory server (LDAP) service name, or ODP.NET connection string. Organize connections by folder, workspace or user scopes. Choose a default connection to automatically connect when opening many SQL scripts. Choose a color to associate a particular connection with a results window. Other options include connecting with a proxy user, setting the current schema, and using a login SQL script.

Create and manage Oracle Autonomous Databases

Create and manage Oracle Autonomous Databases (ADBs) using the Oracle Cloud Infrastructure Explorer tree control. Create new Always Free or paid ADB instances, and start, stop, and terminate existing ADB instances. Automatically create a walletless database connection or download credentials files.

Oracle Database Explorer

Explore your database schema in a tree control. Right click on the connection name to open an existing SQL file or create a new file that uses that connection. Show table data and generate CREATE, SELECT, INSERT, or DELETE SQL for tables. View, edit, debug and run stored procedures and functions. Explore other users' schemas. Use filters to show only those schema objects you are interested in.

PL/SQL debugger

Debug PL/SQL in stored procedures, functions, and packages using Visual Studio Code's native debugging features. Compile PL/SQL for debugging, step into PL/SQL, and run to a breakpoint. Enable External Application Debugging to listen for and debug PL/SQL procedures and functions called by applications or SQL scripts.

Use IntelliSense to autocomplete schema objects

Begin typing SQL into the editor and autocomplete suggestions will appear. Enter a schema name, schema object name, or alias followed by a period (".") to show the child objects. Enter a stored procedure or function name and autocomplete the parameter list. Modify the settings to choose whether to use upper or lower case. Configure filters to limit IntelliSense suggestions to only those you are interested in.

In the examples below, a stored procedure will autocomplete as you type - or you can choose to fill in a template for the call:

Navigate SQL and PL/SQL code with Hover, Go to, and Peek

Use powerful editor features to navigate and learn more about SQL and PL/SQL objects. The editor supports hovering, Go to/Peek Definition, Go to/Peek Type Definition, Go to/Peek Implementation, and Go to/Peek/Find All References.

Navigate through scripts using breadcrumbs

Use breadcrumbs to easily navigate through large scripts.

SQL and PL/SQL formatter with configurable settings

Format SQL and PL/SQL via a single click on the toolbar. The formatter settings offer a wide variety of options.

View table data and save as CSV or JSON

View the results of queries in a grid and save the results in CSV or JSON format.

Problems panel integration

Errors encountered when running SQL scripts or when saving PL/SQL to the database are placed in the Problems panel. Clicking on the error takes you to the line in the script where the error occurred.

Explain Plan and Execution Plan

View the Explain Plan to see the predicted plan for a SQL statement. View the Execution Plan to see the actual plan used for an already executed SQL statement. View plan results in a grid or text format that is configurable via the settings.

Code snippets

Code snippets provide example templates for various SQL commands. They are shown when you type 'oracle':

Test stored procedures and functions

Use the Oracle Database Explorer "Run" menu item to run a procedure or function, enter input parameters and view output parameters. If the output parameter is a cursor, you can fetch and display the results.

Bookmarks and history

Save SQL and PL/SQL as "bookmarks" which can later be executed or added to SQL scripts. SQL history is stored for a Visual Studio Code session and can be run, added to scripts, and/or saved as a bookmark.

Changes in version 21.6.0

• Filters on Oracle Explorer (also filters IntelliSense suggestions)
• Organize connections by folder, workspace or user scopes
• Colorized connection node name and code window for easy identification
• Connection dialog LDAP option and other UI enhancements
• SEPS wallet connection support
• Explain plan/Execution plan
• Schema names offered as IntelliSense suggestions
• Configurable query results window location
• Performance and memory improvements, especially useful for very large schemas
• Shared IntelliSense cache between files that use the same connection
• Many improvements in IntelliSense including joins, insert, update and delete
• Formatter enhancements
• Enhanced diagnostic tracing to enable support to diagnose a wider range of issues

Please refer to the Licensing Information User Manual for Oracle Developer Tools for VS Code for additional licensing information including third-party notices and/or licenses.

Copyright (c) 2023, Oracle and/or its affiliates.