Oracle APEX has always included the ability to access the database through SQL and examine objects through an object browser, including (but not limited to) tables, indexes, packages, procedures and functions. This has always been acceptable to use for small database development tasks, but for significant database development, we have always had access to full IDEs like SQL Developer, a free tool built by Oracle. Now, there is a new tool on the block, SQL Developer for VS Code.
In 2023, Oracle announced they would be moving SQL Developer from the current implementation as a standalone Java-based Windows application to an extension for Microsoft’s Visual Studio Code. VS Code is a powerful development IDE with thousands of extensions to cover almost every possible feature with a powerful text editor at its core, perfect for writing code and SQL. The SQL Developer extension became available to the public in January 2024.
Eventually, Oracle hope to move all the features from the SQL Developer IDE into SQL Developer for VS Code. However, it is unsure how this will be accomplished currently. Oracle may decide to create several smaller extensions that replicate the functionality, and therefore only those required would be installed.
Oracle APEX has evolved significantly over the years and provides access to all the commonly used user objects, as well as running standalone SQL and SQL scripts, amongst others. The user interface has changed over the years, but currently, the SQL Workshop in APEX 23.2 looks like this:
The Object Browser, SQL Commands and SQL Scripts are accessed through the SQL Workshop, and it is here that using APEX as a database IDE becomes a little troublesome. As APEX is web-based, running in a browser, the usual setup for a developer will be to have two browser tabs open, one showing the APEX App Builder and one showing the site as it is rendered to the end user. If you need to generate some SQL for a report, for example, you can use the SQL Commands to build and execute the query until you are satisfied and then copy this back to the report in the App Builder. The issue is that you must switch from the App Builder to the SQL Workshop/SQL Commands, which cannot co-exist in the same browser tab. It is possible to have two tabs open, one with the App Builder and one with the SQL Workshop (three if you include the rendered site). However, we will still hit issues when doing database development alongside the application building.
As you can see in the screenshot above, you can only have the Object Browser or the SQL Commands open at the same time in one tab, so using the SQL Commands to create a query that will be pasted into a package procedure means a lot of switching between the two, and to get a list of tables requires either opening up a separate browser window or switching between the object browser and SQL Commands. This can be accomplished using many browser tabs but quickly becomes difficult to manage.
This can be time-consuming and confusing, especially if it’s not something you do on all development projects and only experience this occasionally. Although APEX provides all the tools needed, it can be more beneficial to utilise an external IDE to handle the database changes and leave the application building to APEX.
Several powerful, feature-rich IDEs for Oracle development include SQL Developer, TOAD and PLSQL Developer. These are standalone Windows applications that can be huge in size and complexity and take a long time to become a power user. Most developers will never utilise every feature due to the breadth of features. TOAD, for example, has modules for DBAs to analyse the database and perform their daily tasks, something day-to-day developers probably will not need.
Over the past few years, Microsoft’s Visual Studio Code (or VS Code) has come to the fore as one of the tools of choice for developers to create their own code. It has a powerful text editor with extensions for all the main code templates for colouring keywords, auto indenting, parameter matching, etc, and the editor itself is hugely customisable to suit the end user. It can also integrate with version control repositories. Oracle saw that many developers were turning to this tool and so decided to leverage this by building SQL Developer as an extension to VS Code. The first public release was in Jan 2024.
The future of SQL Developer as a standalone product is currently deprecated. All future development will shift to SQL Developer for VS Code, and eventually, all features will be imported. Currently, only the main developer functions exist, which means this APEX external IDE is perfect for limiting the issues described above without providing any feature bloat.
To install the extension, open VS Code and select the “Extensions” icon on the left-hand side that looks like this:
In the search bar at the top of the Extensions sidebar, enter “SQL Developer”, and you will see a list of matching extensions. The one you will need is called “Oracle SQL Developer Extension for VS Code”. Go ahead and click on “Install”. Once finished, you will see the extension listed in your extensions like this:
You should now have a new icon down the left-hand side of VS Code, which is SQL Developer for VS Code, like so:
Click on the icon, and SQL Developer for VS Code will launch. The first thing you will see is a list of SQL snippets you can use at any time, together with the ability to connect to your database. Create a connection to your database. Both local and OCI databases are supported using the usual connection settings.
SQL Developer for VS Code was initially created to provide the features required by developers, without the bloat of features prevalent in large-scale external IDEs. Therefore, functionality is limited to SQL execution, object interaction and code maintenance. There are no DBA features or other complex functionality to get in the way right now.
Once you have connected to your database, you will see an expandable tree list of all the objects you can interact with. Although the overall database features are limited, the number of object types is significant and extensive. Here is a list of all the available objects that you can utilise at this time:
To write and execute SQL, you will need to launch a SQL worksheet. This is done by clicking on the icon in the banner next to the connection name, and it looks like this:
A new SQL Worksheet will be displayed on the right-hand side of the window. It can be used to type any SQL you wish, just like the SQL worksheet in the standard SQL Developer. To execute, click on the run icon in the icon bar in the top right corner of the editor window. You will be able to run a single SQL statement, run the editor contents as a script, run it in SQLcl (a standalone Oracle SQL command language editor) and even run an explain plan. Executing the code will show the results below as follows:
Accessing any objects will launch their own tab in the editor, which means it is much easier to keep track of SQL Worksheets, package procedure editing, table definitions and contents, and any other function you are utilising. VS Code does an exceptional job of code colouring for easier visualisation and also includes code autocomplete.
I won’t explain all the features of SQL Developer for VS Code here. I’m simply explaining what is currently available, plus I wouldn’t want to spoil the fun of discovering all the features for yourself. But rest assured, SQL Developer for VS Code is a database development feature-rich IDE that only enhances the development experience for those who do not require the full features that standalone IDEs offer but require a little more than the features supplied within APEX itself.
Oracle provides its own online database and APEX development site used to create non-corporate sites to showcase ideas and concepts or allow developers to experiment. This is available at apex.oracle.com and is extremely popular amongst APEX developers. Unfortunately, this site does not allow external connections from any IDE, including SQL Developer for VS Code. So, if you are developing on that site, you will be restricted to using the APEX internal features to access the database objects.
One final potential disadvantage is that Oracle have yet to decide how they will implement the other features of SQL Developer into VS Code. They could bloat this extension so that it’s convenient for all to install a single extension, but that would potentially make the product unsuitable for some. Hopefully, Oracle will build several extensions that the developer can choose to install that suit their exact needs.
SQL Developer for VS Code is a powerful addition to the APEX developer’s toolbox. It allows you to have APEX app development handled through the internet browser whilst maintaining a separate, easily accessible Oracle database connection with most of the tools that you will need to build databases and develop stored procedures efficiently and effectively. The added benefit is it is currently lightweight and does not contain bulky features that are not usually utilised by APEX developers. I hope that as Oracle adds in the features currently available in the standalone SQL Developer, these are added as separate extensions to maintain that lightweight feel.
For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.
Subscribe to Oracle APEX Insights if you want to stay tuned for more APEX updates.