Don’t be a moron. Install APEX in all your Oracle databases!


If you come from an Oracle background you know what Application Express (APEX) is. You know it’s an awesome low-code development environment, and you know it can turn DBAs and PL/SQL developers into productive members of society in no time at all. 🙂

Even if you don’t want to use APEX, you should install APEX anyway, because it comes with some really handy PL/SQL packages. These come to mind.

APEX_WEB_SERVICE : I’ve written my own APIs in the past, but I never use them anymore. I recommend people make web service callouts from the database using the APEX_WEB_SERVICE package. You can use it for REST and SOAP calls, and it works equally well for JSON and XML web services.

APEX_JSON : Oracle included some JSON functionality in Oracle 12.1, but they only completed the implementation in Oracle 12.2. If you are working with a database version older than Oracle 12.2 and you care about JSON, you need the APEX_JSON package.

APEX_DATA_PARSER : This package allows you to convert CSV, JSON, XML and XLSX data into rows and columns. It’s really simple to use!

APEX_ZIP : You can do some simple gzip/gunzip stuff with the UTL_COMPRESS package, but the APEX_ZIP package allows you to zip up multiple files into an archive, or extract files from an existing archive.

APEX_MAIL : This package gives you an API over the APEX mail functionality. It’s more flexible than the UTL_MAIL package, and is much simpler than writing your own APIs using the UTL_SMTP package.

APEX_STRING : I always used to use the STRING_TO_TABLE and TABLE_TO_STRING functions in the APEX_UTIL package. Those have now been moved to the APEX_STRING package. You might prefer to use the SPLIT and JOIN functions instead. There is also a bunch of other string handling stuff worth checking out.

Every release brings more goodies for PL/SQL programmers, regardless of whether you want to use APEX or not!

So do yourself a favour. Install APEX into all your Oracle databases and make your PL/SQL developers happy.



PS. I don’t actually think you are a moron if you don’t install APEX. If you take the title of blog posts seriously, when they are clearly clickbait, maybe you are a moron…

Update: There seems to be a little confusion on some social media comments. Installing APEX into a database doesn’t mean APEX is available for people to develop applications. You can only use APEX proper if you have a gateway (ORDS, EPG, mod_plsql) fronting it. Without a gateway, APEX doesn’t work. Someone can’t take it upon themselves to run a gateway somewhere else if they don’t know all the passwords, so installing APEX and using it are not the same thing. Installing it does make the built-in packages available for normal PL/SQL development, which is really handy. Hence this post.

Author: Tim...

DBA, Developer, Author, Trainer.

2 thoughts on “Don’t be a moron. Install APEX in all your Oracle databases!”

  1. Thanks, but no, I’ll remain the moron. My reason could be viewed as silly, but… I prefer open source even in PLSQL. APEX is not. Why I prefer open source? One example: I needed once to save some considerable amount of PGA during unzipping of pretty large files. I used Anton Schaeffer’s AS_READ_XLSX package, extracted the unzip functionality from it and remade it in a streaming fashion. (UTL_COMPRESS has routines for it.) Could have I done that with APEX_ZIP? I could’ve not, because the package is wrapped, i.e. closed source and proprietary. Second example: Similar case with remaking the sole AS_READ_XLSX package of his to a streaming fashion, now allowing for parsing several hundreds of MB large XLSX files. Could have I done it with APEX’ XLSX parser? I don’t think so, it’s closed source. And so is the case with all Oracle stuff – all closed source and when you miss a feature you’re left with… waiting for it to appear in the next release. As much as I love Oracle as RDBMS, I’m pretty fed up waiting all the time for features to become available to me.

  2. Peter: I think that’s a valid choice, provided you are willing to maintain the code going forward. Anton’s code is always excellent, and some of it has been to build the APEX equivalents. One of the previous releases credited his ZIP package. Unfortunately, not everyone is as good as Anton, and not everyone can commit to maintaining code if he ever stops. For those people the APEX packages are really useful.

Comments are closed.