In today’s video we demonstrate the TRUNCATE TABLE … CASCADE feature added in Oracle 12.1.

The video is based on this article.

The star of today’s video is Øyvind Isene, who reluctantly took a break from his coffee to let me film this clip. πŸ™‚



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.

If you replace ‘enterprise application servers’ with ‘Oracle DB’ – does your message stay same? or different?

Yesterday I put out a post called Does anyone care about enterprise application servers anymore?

Before I posted it I got my boss to read it. This is how the Teams chat went.

  • Boss: It seems fine. Was there anything in particular you were not happy with?
  • Me: I guess I’m a little nervous about the post because the obvious comeback is, “Isn’t that true for the Oracle database too?”, and to some extent I would have to say yes*… I did consider adding a paragraph about that, but thought I would rather not.
  • Boss: Actually that is what I was thinking while reading it.

* In the context of the chat, “yes”, meant is was a valid question. Just not easy to answer.

Sure enough, not long after posting it I got a Twitter response from Timur Akhmadeev that said, “If you replace ‘enterprise application servers’ with ‘Oracle DB’ – does your message stay same? or different?” That is the title of this post. πŸ™‚

I’m going to attempt to explain my thoughts on this by going through the same talking points.

Full disclosure

I’m an Oracle fanboy. You know that. My take on this subject is bound to be biased because of my history.

I know I am mostly known for Oracle stuff, but in my current job I have to look after MySQL and SQL Server databases. I work on one project that uses PostgreSQL, which I’m really bad at. The company recently started using Snowflake, and the plan is to move all analytics and warehouse type stuff to that. So as much as I’m an Oracle fan, my work life is a lot more diverse than you might think.

Third-party application vendors

One of the major points in my original post was about the vendors of third-party products that got rid of enterprise application servers from their stack and replaced them with more streamlined alternatives. Do I see this reflected in the database space?

From my perspective no. We have some products that are 100% Oracle. Nothing we can do about that other than move to completely different products. Of those where the RDBMS engine is a choice, I don’t think we have one where they offer other RDBMS engines, but not Oracle. It seems Oracle is still a viable choice here. I’m not saying they are recommending it, but equally they are not advising against it.

There are some vendors where MySQL is not an option, but PostgreSQL is. Interesting…

Overall, we don’t see the same pressure from the vendors in the database space as we do in the enterprise application server space.


Similar to my comments about application servers, most RDBMS engines can work within containers, but should they? It’s fine for a demo or a small system. Are you happy about running a huge data warehouse as a monolith in a container though? I don’t think this is about Oracle or not. It’s something more fundamental than that.


There is definitely a big learning curve for Oracle. I could argue it’s probably harder to get good at Oracle administration than many other engines. I think you have to be really careful of the comparison though. Take my current company for example. The big and complicated stuff happens on Oracle, and the really simple stuff happens on SQL Server or MySQL. It’s unfair to compare the administration requirements because the use cases are so different.

I think the only way you can compare them is if someone has legitimately done the same types of projects on multiple engines and really understands them. I’m not that person, so I can’t make that comparison.

But what about product X?

As with the application server comment, if you have a vendor/product that demands a specific engine, you have no choice but to stick to it, or change vendor/product entirely.

For new products I would suggest you get some information about their current customer base and go with the majority/trend. If Oracle is an option, but 90% of their customers are not using Oracle, I would not knowingly make myself a minority without good reason.

Moving to the cloud

The cloud is the great leveller, and I think this is both good and bad for Oracle.

In the case of administration, cloud services can hide a lot of the complexity and make things much more generic. Take a look at RDS on AWS and you will see MySQL, SQL Server, PostgreSQL and Oracle all feel quite similar from a basic administration perspective. If people think Oracle is too complicated, take a look at RDS on AWS, or Autonomous Database on Oracle Cloud.

Where it becomes an issue for Oracle is as soon as the guts are hidden from us, how valuable is the Oracle IP? Imagine feature X is baked into the Oracle database, but requires 3 additional apps to achieve the same result with another RDBMS. On-prem this is a real pain, as it’s now my responsibility to get all this stuff working and keep it working. If a cloud services does all that for me, do I care if it is baked into the RDBMS or not? I don’t think I do. I just care about what features the service delivers and the price.

There is also the comparison of “built for the cloud” vs “monolith in the cloud”. I know this concerns a lot of people, but to be honest I don’t care. If the resulting service gives me what I need, how much do I care what’s under the hood? That’s the cloud provider’s problem, not mine.

Regardless of which engine you pick, I think you should assume you will be using a cloud database service unless there is a compelling reason not to, rather than the other way round.


I’m not going to get into the RDBMS vs NoSQL war because I think time has proved it to be redundant. I’ve been listening to this for about 15 years and it seems pretty clear to me the choice depends on the use case.

Some use cases fit really well with NoSQL and some don’t. There are also grey areas between where polyglot engines become interesting, but it all depends where your use case fits on the spectrum…

Imagine a shop that is mostly Oracle, but needs a JSON document store. Do they use a different engine like MongoDB, or just continue using Oracle and use SODA? The answer will be different depending on how you frame the question.

What’s the real problem with Oracle?

I don’t think Oracle’s problem is their tech, or even their prices. I speak to a lot of people and the number one problem they have with Oracle is the shady business practices. People don’t trust Oracle. That is not a good thing to hear from the customer base, especially when there are more alternatives than ever.

Oracle is in dire need of a Microsoft-style transformation, and that can only happen if the senior management at Oracle really want it to happen.

Is there a conclusion?

When you consider the context of the original blog post, not just the title, I don’t think you can just substitute “Oracle DB” for “enterprise application server” into the question. It’s too simplistic.



PS. The answer is, “it depends!” πŸ™‚

Video : APEX_MAIL : Send Emails from PL/SQL

In today’s video we’ll demonstrate how to use the APEX_MAIL package to send emails from PL/SQL.

The video is based on this article.

You may also want to check out these articles.

The star of today’s video is Paul Vallee, who knocked it out of the park with his recent post on hybrid working.



Video : APEX_ZIP : Manage Zip Files From PL/SQL

In today’s video we demonstrate the APEX_ZIP package, which allows us to manage zip files from PL/SQL.

The video is based on this article.

I must admit I hadn’t even noticed this package until Morten Braten mentioned it on Twitter.

The star of today’s video is Patrick Jolliffe, who is now a resident of Portugal.



Updates to Vagrant and Docker Builds (Oracle Patches and Upgrades)

Unless you’ve been living under a rock, you will know there have been a load of software patches and updates released recently. As a result I’ve been constantly updating my Vagrant and Docker builds as each one has dropped. With the release of ORDS 21.1, the main push for this quarter is done.

This is just a heads-up of what’s been happening.

Packer : My Packer builds of OL7 and OL8 Vagrant boxes have been updated and pushed to Vagrant Cloud. This ended up happening twice due to the quick release of VirtualBox 6.1.22 a few days after 6.1.20.

Vagrant : All relevant builds now have the latest Java 11, Tomcat 9, ORDS 12.1 and SQLcl 21.1 versions. Where necessary the database patches are included. I mostly try to do builds with stock releases, so people without a support contract can still use them, but some things require the patches to function properly. If you follow the blog you will already know the Oracle Enterprise Manager Cloud Control 13.5 builds have now been included.

Docker/Containers : Similar to Vagrant, all relevant builds now have the latest Java 11, Tomcat 9, ORDS 12.1 and SQLcl 21.1 versions. Database patches are updated where necessary.

There is still some stuff on the horizon though. With the new version of APEX dropping on the, I expect a new on-prem release soon (see update). There is also the on-prem release of Oracle database 21c, which I’m hoping drops soon. Once it does I will be adding those builds…



Update: APEX 21.1 dropped today (12-May-2021) just after publishing this post. It’s been added to all the builds now. πŸ™‚

Video : Immutable Tables in Oracle Database 19c and 21c

In today’s video we demonstrate immutable tables, introduced in the April quarterly patches for Oracle database 19c and 21c

The video is based on this article.

You’ll notice a striking resemblance to the syntax of blockchain tables, because they are essentially blockchain tables without the rows chained using cryptographic hashes. You can check out blockchain tables here.

The star of today’s video is Connor McDonald of AskTom and 90-slides-per-minute fame. You can check out Connor’s YouTube channel here.



Oracle Enterprise Manager Cloud Control 13.5

The release of Oracle Enterprise Manager Cloud Control 13.5 was officially announced last week, but I think it’s been available for download for a little longer. The first I noticed was the official announcement though. πŸ™‚

That prompted the usual flurry of articles.

As well as the articles, you can find the Vagrant builds of Cloud Control 13.5 on Ol7 and OL8 in my Vagrant GitHub repo here.

Our current installation is running on an Oracle Linux 6 VM, so I was just about to start a new build on a new OS, which would have the added bonus of allowing us to use Oracle 19c under the hood. Now I can go straight to 13.5, which is nice.

I’ve mentioned before, we use EMCLI to add targets and jobs into Cloud Control, so rather than upgrading as I normally do, this time I’ll probably do a clean build on the new kit and gradually migrate services across to it.

Happy upgrading! πŸ™‚



VirtualBox 6.1.22

Nine days after the release of VirtualBox 6.1.20 and we get VirtualBox 6.1.22. πŸ™‚

The downloads and changelog are in the usual places.

I’ve installed it on Windows 10, macOS Big Sur and Oracle Linux 7 hosts with no drama.

I’ll be recreating my Vagrant boxes to use the new version of the guest additions. Thanks heavens for Packer automation!



JSON Database and APEX Application Development on Oracle Cloud Free Tier

If you follow my Sister-in-law Maria Colgan on Twitter, you will already know the JSON Database and APEX Application Development are now available on the Oracle Cloud Free Tier. For a full list of things on the free tier, see here.

I was on a call with Oracle prior to the release of the APEX Application Development Service, and one of the points I made was it should be available on the free tier, but unfortunately that’s not what happened with the initial release. I’m glad to see that situation has been rectified pretty quickly.

When you are trying to win hearts and minds I think it’s really important to reduce the barriers to entry. These services were available on the free trial, time and credit permitting, but I don’t think the free trials last long enough for people to get a real feel for a service. Adding these to the free tier will result in a number of things.

  • More people will be able to try them and decide if these are the correct services for them.
  • People will be able to live with these services for an extended period of time, and get a true feel for them. Unless you are really well prepared, you can easily do very little with a free trial. The free tier allows you to take your time and make sure you are making the correct move.
  • The more people using the services, the more feedback Oracle will get, which will ultimately make the services better.

I’m really glad this decision has been made!

If you are new to Oracle Cloud, I have some resources that should help you get going. Sign up for the free tier.

Oracle Cloud : Free Tier Account Sign-Up

The bottom of that article has links to lots of other Oracle Cloud posts, including these links.

I’ve also got some Terraform articles, so you can learn to automate the deployment of services on Oracle Cloud, including the JSON Database and APEX Application Development services.

Have fun!



PS. If you’re not following Maria on Twitter, what’s wrong with you?