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.

Cheers

Tim…

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.

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.

Cheers

Tim…

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.

Cheers

Tim…

APEX Application Development Service on Oracle Cloud

A few days ago Oracle announced the APEX Application Development Service on Oracle Cloud.

I had a sneak peak of this service last October. I’ve used APEX since it was know as Project Marvel, but despite this I’m renowned as the worst APEX developer on the planet. I think I was invited as a control subject… 🙂

The main thing I’m expecting from any platform is it must be as “administration free” as possible. APEX is a low-code development tool. Some APEX developers will have a lot of database administration skills, but some will not. I wanted to see a service that got people up and developing safely, with as little administration fuss as possible. I think that’s what Oracle have delivered, which makes me really happy.

These new APEX instances are built on Autonomous Transaction Processing (ATP) instances, with some of the functionality disabled. There is a simpler route to create them using the “APEX Application Development > APEX Instances” menu, but you can create them through the normal Autonomous Database creation screens also.

Existing ATP instances can be accessed through the simplified interface, which is a nice touch.

I did my own run through of setting up an APEX instance here. I’m not going to talk much about the service, because there are lots of great APEX bloggers that are a better source of information than me. Make sure you check them out!

Happy APEXing!

Cheers

Tim…

Vagrant & Docker Builds : APEX 20.2 and other updates

The recent release of APEX 20.2 has triggered a build frenzy.

Vagrant

All my GitHub Vagrant builds that include APEX have been updated to APEX 20.2. The builds themselves are unchanged. This was literally an update to the environment files, so it took longer to test the builds than it did to make the changes.

While I was at it, I did a couple of extra updates. I updated Tomcat to version 9.0.39 on all relevant builds, and updated the optional patch script for the single instance database 19c on OL8 build to use the October 2020 bundle patch. The GI bundle isn’t available yet, so I’ve not altered the OL8 19c RAC build. That will happen soon.

Update: I’ve got the GI bundle patch now, and the OL8 19c RAC build has been updated to use it.

There will of course be more updates to the builds once we get the new versions of AdoptOpenJDK, ORDS and SQLcl, that are probably coming soon.

Packer

I mentioned in my VirtualBox 6.1.16 post I would be updating the oraclebase/oracle-7 and oraclebase/oracle-8 vagrant boxes to include the VirtualBox 6.1.16 guest additions. Those are done now.

Docker

This is pretty much the same as the Vagrant story.

The relevant GitHub Docker builds for Oracle database and ORDS containers have been updated to include APEX 20.2.

I’ve also added Tomcat 9.0.39 to the ORDS builds, and updated the optional patch script for the database 19c on OL8 build to use the October 2020 bundle patch.

Once again, more changes will appear as the new versions of AdoptOpenJDK, ORDS and SQLcl appear.

Conclusion

Automation is awesome! A few minutes and we are bang up to date!

Cheers

Tim…

Video : APEXExport : Export APEX Applications and Workspaces From the Command Line

In today’s video we’ll give a quick demonstration of using the APEX command-line export utility.

The video is based on this article, which includes more examples, and Windows-based examples also.

The star of today’s video is my daughter Heli “Hell-Squirel” Helskyaho. Make sure you check out the cloud forming a Pikachu tail above her head! 🙂

Cheers

Tim…

Video : SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

In today’s video we’ll give a quick demonstration of deploying an APEX application using the SQLcl implementation of Liquibase.

I Know what you’re thinking. Didn’t I do this video two weeks ago? The answer is yes and no. This video is very similar to the Liquibase video I did two weeks ago, but that was using the Liquibase Pro client. This video uses the SQLcl implementation of Liquibase, and more specifically the runOracleScript tag to achieve the same thing.

The video is based on this article, which has an example of deploying an APEX workspace and an APEX application.

If you are new to Liquibase and SQLcl, you might find it easier to start with these.

The stars of today’s video are the offspring of Jeff Smith. I had been annoying Jeff on Twitter DMs while he was meant to be on holiday, so I agreed to pay him back by turning his children into international megastars. I take no responsibility for how they handle the fame! 😉

Cheers

Tim…

Video : Liquibase : Deploying Oracle Application Express (APEX) Applications

Today’s video is a quick demonstration of deploying an Oracle Application Express (APEX) application using Liquibase.

The video is based on a new article of the same name, which covers the deployment of both APEX workspaces and APEX applications using Liquibase.

Here’s some other content you might find useful.

The star of today’s video is Jorge Rimblas, making a welcome return to the channel, along with some serious reverb. 🙂 Last time we saw Jorge was in a boxing gym, and his daughters have also taken the spotlight for one video.

Cheers

Tim…

Video : APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns

Today’s video is a quick demonstration of using the APEX_DATA_PARSER package to convert simple CSV, JSON, XML and XLSX data into rows and columns.

If you want the copy/paste examples and the test files, you can get them from this article.

Yet another reason why you should always install APEX in your databases.

The star of today’s video is Kosseila.HD, also known as BrokeDBA, complete with sun glasses, basket ball and a rattling watch. 🙂

Cheers

Tim…

APEX 20.1 : Vagrant and Docker Builds (and Some Comments)

About 2 days ago we saw the announcement of the release of APEX 20.1.

I normally set myself quite an aggressive timetable to get new APEX releases to production. So much of what we do lags behind the curve, to the point where I just want to see it burn, so when I get the opportunity to force the issue, like I do with APEX, I do.

In my typical fashion I move all my builds to the latest release and kind-of forget any prior release ever existed. As result, you will see all my Vagrant and Docker builds have been updated to use APEX 20.1, along with updates of OpenJDK and Tomcat.

The basic installation is the same as always (here), so there is no drama there.

Next week I’ll start upgrading a few of our development environments, and check everything looks OK. Assuming I hit no problems, it will be full steam ahead. Fingers crossed.

The new features are always a big draw for most people, but I tend to start by looking at the deprecated features and desupported features.

  • When I saw APEX_UTIL.STRING_TO_TABLE and APEX_UTIL.TABLE_TO_STRING had been deprecated I was about to lose my shit, then I saw they have just been relocated to the APEX_STRING package. Composure regained. 🙂
  • The desupport of some of the productivity apps is a good thing. Some of them were not so good and having a long list of Meh, is not as good as a smaller list that offer something better. Just my opinion.

As far as the new features in the announcement, here are my initial (and uneducated) thoughts.

  • Redwood. I feel a little “Meh” about this. I don’t love it. I don’t hate it. I guess it makes sense to bring it in line with the current Oracle thang! 🙂 I would have had a little more contrast on the icons. They look quite washed out, but I am not renowned for my design aesthetic. 🙂
  • Faceted Search Enhancements. Love this! When I saw Mike Hichwa demonstrate the first iteration of this I had a When Harry Met Sally – Restaurant Scene moment in my head. All additional functionality is welcome. I think Faceted Search is a great and some might say killer feature.
  • Friendly URLs. My first reaction was, “Oh thank God!”. This was quickly followed by, “Oh my God!”. The thought of what some people will name their page aliases fills me with dread, but it is a welcome addition.
  • Native PDF Printing. Nice.
  • Mega Menus. Love it. The traditional side-bar menu is fine for little apps, but it’s a bit dated, takes up a lot of room and is nowhere near as flexible as this looks. I think I would have made it the default choice, but I can see why sticking with the old style is probably the safer option. 🙂

There are a bunch of other things in the release notes that sound interesting, including remote application deployments using REST Enabled SQL, but I’ll leave you to discover those for yourself.

It’s early days, but this looks like a really nice release… Again…

Cheers

Tim…