28 Feb 2024 Oracle SQL Developer Extension for VS Code

There is no better way to start a new year than with a new Oracle release: the Oracle SQL Developer Extension for VS Code!

As we all know, Visual Studio Code (VS Code) stands out as a powerful editor, largely thanks to its extensions: these tools allow for comprehensive customisation, adding more features to this editor, and providing flexibility and versatility in your workflows. 73% of developers say they use this Integrated Development Environment (IDE), so the chances are that you are already familiar with it!

Now, despite the substantial number of extensions that VS Code already offers, we can add one more to the list – the Oracle SQL Developer extension.

This new extension, released in mid-January 2024, is bound to be adopted by developers as part of their workflow. In addition to providing the ability to execute SQL queries and scripts, to interact with schema objects in this database, etc., it will also offer the same user experience that we already know, but with a more modern and powerful foundation. It is worth mentioning that Oracle stated that they couldn’t include all the features in the initial release, but they have promised quarterly updates.

This extension handles Oracle Database versions 11g to 23c, regardless of the deployment environment (on-premises, VM, container, cloud, etc.).

Join us as we unveil its power and discover which updates will shape its future!

Setup

Installation and Connections Tutorial

Before we dive into explaining how and where to install the new extension, we need to have the VS Code tool at our disposal. Once it is installed, navigate to the left-hand side of the panel, where you will find the Extensions icon. Click on it and search for “sql developer extension” in the search bar, then install the provided extension as shown below:

After the installation, you should see the following icon in the left-hand panel:

If you want to create or add a new connection, just click on Add Connection or (Ctrl + Shift + N) and fill in the required information:

There are several connection types:

• Basic: The most common, used to connect to an Oracle Database running on a local or remote machine.
• Custom JDBC: To connect to a non-Oracle database, such as MySQL or Microsoft SQL Server.
• Cloud Wallet: To connect to an Oracle Database hosted in the cloud, using your Oracle Cloud Autonomous Database Wallet.ZIP files, which contain the necessary credentials and configuration settings.
• TNS: To connect to an Oracle Database running on a network. Use your TNSName.ORA files to define the network addresses and configuration parameters.

Currently, the user interface (UI) supports the creation of database connections on an individual basis only. This means that users looking to migrate or set up multiple connections simultaneously face a limitation, as there is no direct option to import a file with pre-existing connections from SQL Developer (classic) through the UI.

However, there’s a practical workaround for this limitation: by leveraging the SQLcl terminal, users can bypass the UI’s constraints and import their pre-existing connections efficiently. Let’s see how to do it!

First, navigate to the top of the panel and click on New Terminal. Once the terminal opens, move to the right, click on the plus button (+), and select the SQLcl option.

Before proceeding any further, it’s essential to prepare your connections for import: go to the SQL Developer Tool and export the connections you wish to import. Save them in JSON, the only format supported for this process. Now return to the SQLcl terminal and specify the path to your JSON file to begin the import process.

Now follow these commands, replacing them with the names of your files:

1. secret set connections oracle
2. connmgr import -key connections json
3. conn -name Connection1

With these three steps, your connections will be imported into VS Code. For more information about exporting connections, you might want to take a look at this Jeff Smith blog post.

Oracle SQL Developer vs Oracle Developer Tools Extension

But wait. Isn’t there an Oracle extension that does just that?

You might have realised that in 2019 Oracle introduced an extension called Oracle Developer Tools for VS Code:

Both extensions offer loads of functionalities, making them invaluable for working with SQL and PL/SQL in Visual Studio Code. However, the newer extension, the focus of this blog post, is designed to integrate seamlessly with Oracle SQL Developer, a tool that operates independently of Oracle. So, what makes one better than the other?

In the evolving landscape of Oracle development tools, there are currently two extensions that serve distinct purposes for database professionals: firstly, the Oracle SQL Developer, a well-established tool, widely used for editing and executing SQL scripts within Oracle Databases, whilst the newer Oracle SQL Developer Extension for VS Code is aimed at providing an end-to-end development solution that can eventually replace SQL Developer. And don’t forget that it will offer many more functionalities in the future as Oracle are committed to releasing new versions, whereas the future update trajectory for the classic Oracle SQL Developer is not so clearly defined.

Throughout this blog, we will be comparing the new extension with the SQL Developer standalone IDE.

Common Functionality – Key Features

In this section we’ll highlight some key features already present in the classic SQL Developer and in the SQL Developer Extension for VS Code, to get a better understanding of the capabilities and advantages of each.

Connection and Object Browser

We can still search through our connections and objects within the database in a side panel with a tree structure for navigation, just like in the original SQL Developer:

In the new extension, users will notice the absence of several sections available in the standard SQL Developer, including XML Schemas, OLAP Option, Analytic Views, Scheduler, Property Graph, and RDF Semantic Graph. However, this new version introduces exclusive features such as Application Express and DBMS Jobs.

Currently, the new extension does not feature an object finder tool, limiting object searches to specific categories. For instance, to locate a table, users must navigate to the table category and use the Ctrl + F shortcut to search:

But it is a long way from “Find DB Object” in the older SQL Developer:

We can also use Breadcrumbs to navigate, and we’ll look at this in more detail later.

SQL Worksheet

This is where most of your work will take place as an SQL developer: it consists of a writing space, tabs, and buttons, allowing us to run a statement, run a script, run in SQLcl, explain a plan, and attach/detach a connection. Additionally, we can split the editor vertically and horizontally, various times if we want:

Quick Fixes for Errors

There are quick fixes for errors as in the standard SQL Developer. In the new version you can still see a possible solution but cannot click on it to apply it:

This is possible in the classic version:

However, there is now a Problems tab that shows all the errors in all open files and worksheets:

VS Code also highlights tabs in red to indicate problems within them, alerting you to errors and offering details on the number of problems when you hover over the tab:

Explain Plan

This feature plays a crucial role in SQL optimisation by outlining the detailed steps Oracle Database takes to execute a given SQL statement. When activated, it generates an execution plan that inserts a row for each step into a designated table, typically referred to as the plan table output. This output provides invaluable insights, including the sequence of operations, the access methods and join algorithms used, and the areas where performance could be enhanced.

The plan table output also details the cost associated with executing the query, incorporating factors such as CPU usage and I/O cost, amongst others.

All these steps help you to better understand the sequence of operations performed by the database during the query execution. Considering the following query, we obtain the representation shown below of the query’s explain plan:

select * from LATAM_ANALYTICS.DIM_ASSET;

Live Code Completion

As we type, VS Code automatically suggests what we might type next: just hit Tab to apply the top suggestion, or scroll down with the arrows to choose a different one. It works faster than in the classic SQL Developer and provides lots more options too:

New Features

Here we’re going to highlight the new features in the latest version, which are invaluable in our daily tasks. These enhancements significantly facilitate code creation and file navigation within our respective repositories, making the process both easier and more intuitive.

GitHub Copilot

The SQL Developer extension includes a live code completion feature, enhancing our code-writing experience. However, for even more advanced assistance, among the many extensions available we can leverage GitHub Copilot, an AI-powered tool that not only offers on-the-go code suggestions but can also generate whole queries or answer queries on various topics. Drawing from the vast number of projects already posted on GitHub, it accurately predicts the next lines of code, supporting virtually any programming language imaginable:

For the suggestions to start rolling in, simply start typing in a worksheet. You can accept the suggestion with Tab or cycle through additional options if available. The tool will then start generating code line by line until the block is finished:

Bear in mind that Copilot cannot see the structure of your database, so it assumes table and column names. However, it can read and reference other code in the worksheet to improve accuracy in naming.

You can also generate query lines directly: just describe what you need in a comment, then press Tab a few times to accept suggestions and your query is complete:

On top of that you can ask any question by writing it after a commented out “q:”:

Alternatively, bring it into the side panel to interact via a chat box. You can even reference content already written in the worksheet:

To get GitHub Copilot up and running, install its extension, log in with your GitHub account and subscribe, which costs $10/month for individual users or $19/month for businesses. There’s a one-month free trial available too!

Whilst there are alternatives like Blackbox, many offering free tiers, Copilot remains the premier AI-assisted development tool, especially for those seeking comprehensive support across various programming languages. However, it’s worth noting that not all alternatives support SQL, such as Tabnine.

You can always use AI chats in a browser or in various ChatGPT-integrating extensions available in the VS Code Marketplace. You won’t have to switch windows during chats, but they lack live code completion, and in-code references would require copying and pasting code into the chat box.

The ChatGPT extension offers several valuable features, among which the following stand out: firstly, it allows you to select a portion of code and provides a detailed explanation of its functionality, and secondly, it includes a «refactor and find problems» feature, designed to enhance the design and quality of your code by optimising and identifying errors.

Similar to GitHub Copilot, this extension makes it easier for us to understand the code and facilitates its implementation.

Integrated SQLcl

Go to the terminal button and click on New Terminal. Then, on the left, click on the + button and select SQLcl Terminal:

Alternatively, use the Ctrl + Shift + Enter shortcut to execute the highlighted query from the code editor above it:

When opening a terminal, you can see the SQL history in detail:

Breadcrumbs

After loading a file into VS Code, the Breadcrumbs navigation bar lets you navigate files and folders to swap them on the go:

We can also see the structure of the file itself for easier navigation in longer queries:

Similarly, swapping your connection, database, and objects within it becomes streamlined after going into details about an object:

Scroll Ribbon

This works like a normal scroll bar but shows a zoomed-out view of your code for better navigation, especially when you’ve got a lot:

Generating Queries for Objects

Drag and drop an object from the left Primary Side Bar into the code editor to insert the object’s name or generate a statement related to it:

Result of the Insert option:

SQL Snippets

In the left Primary Side Bar, SQL snippets are available in the Connections tree. These can be dragged and dropped into your code to generate solutions for both common and less common SQL challenges:

Pin Tabs

Query tabs can be pinned by selecting the option in the tab’s context menu. Once pinned, the tab moves to the left of the tabs bar and cannot be closed without first unpinning it:

Current Limitations

With so many great new features, there are still some things missing: we mentioned the difficulty of importing database connections at the beginning of this article, and below are some more limitations, although we believe that there is a strong chance that these features will be added in future updates.

Results Filtering

Currently, the results table lacks a feature for filtering query results, although sorting capabilities are available. As an alternative, users could export results to various formats for further analysis in external tools; acceptable export formats include CSV, HTML, JSON, Text, and XML:

Exploring an existing table is like in the traditional Oracle SQL Developer tool, allowing exploration through columns, constraints, dependencies, partitions, and other elements. All available options can be viewed in the attached image below:

If you select the Data tab, it gives you some options that are in the classic SQL Developer too:

The first feature to highlight is the Insert tab, which adds an empty row at the top of the table, requiring users to input new data. Following this, there’s the Export tab; as mentioned earlier in this section, this allows the export of data in various file formats.

Another option is Delete Selected, which will delete all the selected rows as its names suggests. Other handy options include Commit, which ends the current transaction and ensures that all changes made are permanently applied, and Filter, which enhances data visibility and accessibility, allowing you to apply specific criteria to streamline the display of data. Creating a new filter is straightforward: fill in the required fields then select the Apply button. And let’s not forget the Undo tab,  to reverse any changes made.

No Commit or Rollback Buttons

In this extension, there is neither a Commit nor a Rollback button, nor are there working keyboard shortcuts to perform such actions as there are in the classic SQL Developer, so we need to use code for these operations:

No Creation Wizard for Objects

The latest SQL Developer extension currently lacks a feature to create objects, such as tables, using a creation wizard that eliminates the need for coding:

We can still edit our objects via the UI, but not create them:

What’s next?

To wrap it up, we have to say that this new extension boasts a great many strong features. The user experience has improved significantly, and we expect it will only get better in future iterations. We’ve also heard that in about two years’ time the classic Oracle SQL Developer tool will be officially phased out and we suspect that this tool will not release any more updates. Nevertheless, Oracle guarantees quarterly updates for the extension. Until then, we can keep on working with either, be it the traditional tool or the extension we’ve looked at today.

Some of the updates expected in the future are:

Our team here at ClearPeaks is ready to assist you in exploring the full potential of the new Oracle release: SQL Developer Extension for VS Code. Connect with us today to enhance your database management strategies and to stay on the ball when it comes to developments and advances in Oracle technologies!