APEX SQL Workshop – a One-Stop Shop for APEX Development Projects?

In this blog, I will review the current capabilities of APEX Builder SQL Workshop and review to what degree a developer can use it exclusively on development projects.

In recent years, the functionality of the APEX Builder, SQL Workshop, has evolved into a really usable tool for any PL/SQL development that may accompany an APEX project. However, in my team, at least, it’s rare that developers would work on a project and not use a third-party tool, such as Oracle SQL Developer, to do the bulk of the work when it comes to writing code, querying the database, executing DDL statements, etc.

I thought it worth investigating whether we are at a tipping point yet where the inbuilt capabilities match these third-party tools and allow the developer to use APEX Builder as a ‘one-stop-shop’.

Examining SQL Workshop in APEX Builder

SQL Workshops provide an excellent experience for most of the basic requirements that a developer might have when using such a tool. I.e. you fundamentally want to:

• Navigate around and view database objects such as tables, packages, triggers, etc.
• Interrogate the data itself by writing custom queries.
• Script changes to database objects or update data itself.

Using the object browser, you can pretty easily navigate around code-based objects and update them, view tables and their definitions and make modifications if required.

Like most development kits, table definitions provide links to related objects, such as triggers and sequences. SQL workshop also makes it easy to download code – either as individual scripts via the object browser or in bulk (using the Generate DDL utility).

SQL Workshop lays out the object browser in much the same fashion as other database tools would, allowing you to get a good overview of what’s in a particular schema. The list of object types is not as comprehensive as you would get in Oracle SQL developer. For example, there is no ability to create and manage database scheduler jobs – a feature that is essential if jobs are part of your project.

The SQL command window is another major part of the SQL workshop. 

Here, you can execute ad hoc SQL commands to interrogate data and run anonymous functions and procedures. You can access basic performance data on recently run statements as well as view the history of recently run commands. One feature I find particularly useful is the download option, which instantly puts the output of a query into a CSV in your download folder – something which is a little more fiddly in other tools.  

One issue with using SQL commands for DML commands is the fact that, in recent versions of APEX, every statement is auto-committed. You should never be making unscripted direct changes to prod data or even accessing a Prod instance via the APEX builder, so most of the time, it isn’t a problem. However, it still makes me nervous if I’m modifying a small amount of test data – given that if I get it wrong (or don’t select the whole statement), I might end up modifying a LARGE amount of data by accident. 

(oops…)

Having the safety net of being able to roll back DML statements is useful when testing any DML functionality. The reason behind the auto-commit is due to security and the session-based nature of how APEX connects to the database, as it does not have a constant connection and therefore, a persistent session state is not maintained.

It is the very web-based nature of the APEX/SQL Workshop tool which gives rise to most of the limitations of SQL Workshop. When developing, the ability to hop between viewing code, writing queries, examine tables is crucial. While it has improved greatly over the last few iterations, SQL Workshop still doesn’t really allow the fluidity of movement that other tools do. You have to do one thing at a time, as you can’t open multiple tabs. One common trick I use is to open two windows on the browser, one for Object Browser and one for SQL Commands. This works pretty well most of the time. However, you will run into problems if you try to have two object browsers open at the same time in the same session, for the same reasons that it’s not a good idea to open multiple tabs in the same APEX front-end app session – the web framework approach is just not designed to handle that.

The ability to have multiple tabs and be able to navigate around packages and SQL worksheets etc., is crucial when developing code and third-party tools also for this more seamlessly than the APEX builder offering. One counterargument is, of course, that one may end up with far too many tabs open and that the fact that the SQL workshop doesn’t allow for this actually encourages a level of discipline that could lead to more productive working!

Another area where the SQL Workshop doesn’t provide the optimum experience is when a project is version-controlled and has multiple developers. In our team, a standard rule to avoid losing work is to keep code in a version-controlled repository and then open it from that repository when it is being modified. This means that a developer is never just directly modifying the object on the database. So, if two developers modify the same package without realising, the second compilation will overwrite the first, but at least the code from the first won’t be lost. SQL Workshop doesn’t lend itself to working off local scripts. You can’t simply open a local file and compile it against the database like you can with other tools.

Where the SQL Workshop shines is in certain areas over and above the basic functionality, generally within the SQL Utilities section.

Utilities such as data workshop, data-generator and methods on tables seek to make the mundane elements of APEX projects (although not unique to APEX) much easier, such as basic DML, package creation, bulk data upload/download and test data generation.

AI is being gradually plugged into the developer experience of APEX, and whilst there is not yet any intelligent predictive/auto-completion in the SQL Workshop like we have in the app builder, the capabilities of AI are being leveraged into the wider database functionality. A good example of this in the latest version of APEX (24.2) is AI-Driven Data Modelling.

The ability to use AI to quickly generate sensible and normalised schemas for your project is far beyond what traditional client-based tools can achieve. In this case, the web-based nature of the APEX builder is an advantage rather than a hindrance. One can only assume in coming releases, the AI capabilities, in relation to development, will continue to grow.

Other tools – what they can do that APEX can’t (yet)

I’ve already covered some of the basic things that SQL Workshop doesn’t do quite as well as other DB development tools. Now, I’ll look at a few common features that other tools have that might make them indispensable in certain projects. For the purposes of this blog, I have considered Oracle SQL Developer (OSD) and PL/SQL Developer (PSD) only (although honourable mentions for Toad and Visual Studio Code with Oracle plugins).

One of the most notable advantages of OSD and PSD is the ability to navigate around multiple objects and tabs, as already mentioned. This advantage is extended when you consider the ability to move between multiple environments. By its very nature, APEX Builder focuses you on one workspace and the schemas within that. OSD/PSD allow you to have multiple connections at once, meaning if you are working in several environments during the day, you are not having to open multiple different windows. Much like the multi-tab experience, this isn’t always a good thing – with APEX, you know exactly where you are and there is no danger that you might apply code/updates, etc., to the wrong environment. However, having the flexibility to move between databases can be extremely beneficial in many circumstances.

One example of this is the database diff feature in OSD, which allows you to do a full comparison of database objects across databases.

Performance tuning is key for any database project. OSD/PSD provide far more options in terms of generating explain and execution plans, allowing you to analyse the performance of your code. This also extends to database session monitoring, which enables you to view and potentially even stop any long-running database processes.

When a project involves debugging complex code, PSD has an invaluable tool, the step-through Debugger. If you have a lot of complex code with multiple levels of package calls, the Debugger can allow you to pinpoint exactly where something is failing or the output is not as expected. You can put breakpoints in code to run to a certain point. The step-through process allows you to control the execution of each line of code, so you follow the execution of a particular process through multiple procedures and functions.

Another nice feature of PSD is the code beautifier, which can even be configured to your particular preference.

Conclusion

There is no doubt that SQL Workshop is an excellent feature of the APEX Builder and provides a great working environment for the bulk of an APEX developer’s needs. It will continue to grow, especially in the AI space, and one day could well be a one-stop shop for APEX development. If the project is relatively simple and doesn’t require large amounts of code objects, performance tuning or debugging, then it is a viable option, but, for anything else, I will be keeping my Oracle SQL Developer/PLSQL Developer applications on hand for a little while yet.

For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.

Contact us today, and one of our expert developers will be in touch.