How to use Oracle SQL Developer extension in Visual Studio Code

In today’s fast-paced development environment, productivity often depends on having the right tools in a single, seamless workspace. For database developers and administrators working with Oracle, switching between multiple applications to write queries, manage schemas, and debug can feel both time-consuming and disruptive. That’s where Visual Studio Code (VS Code), with its lightweight design and rich extension ecosystem, becomes a powerful ally.

The Oracle SQL Developer extension for Visual Studio Code (VS Code) is part of the Oracle Developer Tools for Visual Studio Code. It provides functionality for working with Oracle databases, similar to what one would expect from Oracle SQL Developer, but directly within VS Code.

Let's see how you can set up and use the Oracle SQL Developer extension from within Visual Studio Code.

- Click on Extensions option in the left pane

- This will bring Extensions Marketplace. Let's search for 'oracle sql developer' here.

- Click on the search results and it should open a new tab on the right side for 'Oracle SQL Developer Extension for VSCode'

- Let's click Install to install the extension.

- Installation usually takes less than a minute. Once installed, we should be able to see SQL Developer option in the left pane.

- Once we click on it, we should be able to see SQL Developer Navigation pane where we will be given an option to create a new database connection. Click on 'Create Connection'

- Let's create a new database connection. I'm going to connect to my ADW database that has been provisioned in OCI.

Enter Connection Name, Username/Password, Connection Type is going to be Cloud Wallet in my use case, but it can be set to Basic by providing the Host, Port etc. details as well.
I'm going to select the Wallet file I obtained from my ADW console in OCI.
In the end, we will Test the connection and Save it.

 
- Once done, we will see our connection in left pane. Let's right click and Connect.

- Once connected, we can expand various sections such as Tables, Views, Packages etc. to see all our existing database components. This is very similar to what we see in the SQL Developer tool.

- Let's open the Invoice table and we'll be able to see the details in a new tab on the right side. We can navigate through various sections such as Columns, Data, Grants, Indexes etc. very similar to how we can access them in SQL Developer tool.

- Let's go to data tab and insert a new record and fill in the values in respective columns. As we can see here, it automatically builds an Insert statement using our data and issues a commit implicitly.

- We can utilize additional tabs such as Dependencies where we will be able to see the dependent objects which are using the Invoice table. As we can see Invoice_Details_V view references the Invoice table, hence it's listed here.

- Similarly, we can access various code units such as Packages, Procedures, Functions etc. from the left pane. Here, we can see it shows us Package definition as well as Package Body details along with the sub-procedures under the packages.

- If we click on one of the procedures from within the Package then it will open the source code on the right side.

- Let's modify the sample code to add a test line and click Compile. This should recompile the package with our changes.

- Now, let's see how we can access the SQL CL terminal from within the VS Code.

- Navigate to Terminal tab. By default, it should show the PowerShell prompt like this.

- Let's right click on our database connection (ADW in this case) and select Open SQLcl option from the dropdown.

- It should connect to the database server in a new session and present us the good old SQL prompt.

- This is our typical SQL prompt where we can issue any SQL commands the way we would normally do on the traditional SQL prompt.

Conclusion:

As we can see, with the Oracle SQL Developer extension, we can work on our Oracle database directly from within VS Code IDE, without switching to a separate tool like Oracle SQL Developer or any other database management tool.

We get all the features of VS Code, such as syntax highlighting, code formatting, and version control integration, along with Oracle-specific features that provide us SQL, PLSQL support, Database exploration and management and rich debugging and testing capabilities.

By using the Oracle SQL Developer extension in Visual Studio Code, developers can significantly improve productivity and streamline their Oracle database development process, all within a single, powerful, and lightweight editor.