Video : FOR LOOP Iteration Enhancements in Oracle Database 21c

In today’s video we demonstrate the FOR LOOP iteration control enhancements in Oracle database 21c.

The video is based on this article.

If you’re not already got up to speed with qualified expressions in 18c, you might want to check this out also.

The star of today’s video is Alex Nuijten, of PL/SQL and in recent years APEX fame.



Video : Using Expressions in Initialization Parameters in Oracle Database 21c

In today’s video we demonstrate using expressions in initialization parameters, introduced in Oracle database 21c.

The video is based on this article.

The star of today’s video is Deiby Gómez, who is a fellow Oracle ACE Director, and was kind enough to take me sightseeing when I visited Guatemala for a conference.



You don’t need to be an expert to be useful!

I come from a time when you could be an expert at one thing and be really useful to a company, but I think that time is long gone. If you only have one skill, no matter how good you are at it, you probably can’t achieve anything in tech without waiting weeks to get people to help you with all the stuff you don’t understand. In recent years, being a tech allrounder seems to be much more useful than being an expert. Maybe it always was.

Of course, this poses its own set of problems. How do you learn all this stuff? That’s the hard part and there aren’t any short cuts. There isn’t a “full stack developer course” that will teach you. You’ve just got to work on your Google-Fu and start getting your hands dirty. On the positive side, there is a lot of good information out there to get you started. Blog posts and videos that will get you from zero to adequate in a short amount of time if you put in the effort.

Over the last few years I’ve played with a lot of different technology, and I still find new stuff interesting, but it’s taken me a long time to deal with the fact I’m crap at most of it. Good enough to get the job done and fool people into thinking I know what I’m doing, but ultimately only one weekend of playing with the tech and a couple of Google searches ahead of some other people.

So my advice to people in tech is:

  • Try and get involved in as many aspects of tech as possible.
  • Forget trying to be an expert in any of them. Just try to get good enough to be useful.
  • Be humble enough to realise that what you say and do today may change tomorrow when you’ve Googled a bit more.
  • Try to understand the big picture. How things fit together and how processes work. Programming languages and services change all the time, but understanding the goal and the processes to get there don’t change as much as you might think.

Remember, it’s just my opinion!



Video : Partial Indexes for Partitioned Tables

In today’s video we demonstrate partial indexes for partitioned tables, introduced in Oracle database 12.1.

The video is based on this article.

The star of today’s video is Carry Millsap, who is being lead astray by a very naughty James Morle.



Video : JSON_TRANSFORM in Oracle Database 21c

In today’s video we demonstrate the JSON_TRANSFORM function, introduced in Oracle database 21c.

This video is based on the following article, which has a lot more information and examples than the video does.

I’ve written a bunch of stuff on JSON and ORDS, and there’s a YouTube playlist.

The star of today’s video is Tom Kyte. Here are two funny (to me) stories about my interactions with Tom.

On my first visit to OpenWorld in 2006, Tom walked up to me and introduced himself. I was blown away he even knew who I was. It was a massive ego trip. On my next visit to OpenWorld in 2007, I walked up to Tom and said casually, “Hi Tom”. He looked at me, looked down at my conference badge, looked at me again and said, “Hi Tim”. Needless to say, my ego was placed firmly back where it belonged. I still laugh when I think about it now.

At a later OpenWorld I was sitting at a table and someone was fanboying me. Tom came along and asked if he could sit at the same table. I said sure. My fanboy turned and said in a rather snarky tone, “And who are you?” Tom replied, “Hi. I’m Tom Kyte”. Fanboy’s face immediately dropped and at that exact moment in time I became invisible, and fanboy began to fanboy Tom. It was priceless!

Good times!



Long Term Releases, Innovation Releases and Preview Releases?

I feel like this has been said a lot over the last year or so, but I still keep getting questions about it, so I’ll chip in.

Before we jump into it, the first place you should be looking for release information is this MOS note.

Release Schedule of Current Database Releases (Doc ID 742060.1)

With that in mind, here we go…

Long Term Releases (Long Term Support Releases)

I prefer the name Long Term Support (LTS) Releases, as that is really what they’re all about.

The important point here is you get 5 years of Premier Support followed by 3 years of Extended Support. This will be a more manageable upgrade cycle for a lot of companies. I can see many companies just jumping between long term releases and never venturing near an innovation release.

Oracle database 19c is a long term release. The next one is scheduled to be 23c.

Innovation Releases

An innovation release gives you the bleeding-edge functionality in the Oracle database. Oracle call it leading-edge. I’m sticking with bleeding-edge.

The main thing here is you only get 2 years of Premier Support and no extended support. If you go this route you are committing to a quicker upgrade cycle. In practice, this really means yearly as far as I’m concerned. That’s not the sort of commitment I expect many companies to make.

Oracle database 21c is an innovation release, so I don’t expect many people will actually get to use it in their production systems. That’s not to say they shouldn’t, just that it may not be practical from a resourcing perspective in their company.

What I can see happening is a company will avoid innovation releases unless there is a killer feature they need now, which makes them willing to accept the regular upgrade cycle until that feature makes it into the next long term release. That might mean most databases in their estate are on long term releases, but a couple of databases are using innovation releases.

When you’re reading articles and blog posts about features in innovation releases, just remember you may not get to use them in production for quite some time, unless they are backported to the previous long term release.

Preview Releases

This is not a “planned thing”. I don’t think we expect to see preview-only releases in future. Oracle database 20c was available in preview on Oracle Cloud, but the crazy times of 2020 meant it never made it to general availability. Instead we jumped straight to 21c, as if 20c never happened. As a result, many of the new features in 21c, were originally intended to be new features in 20c.

Once again, I think the 20c issue has been discussed a lot of times, but I still get questions about what happened to 20c. 🙂

What You Should Do

At minimum you should be trying to get all your databases to 19c. You can then stay there until 23c is released, which will be your next big push.

If your company can cope with a quicker upgrade cycle, and if there is a compelling reason to do so, you can consider innovation releases. If you use an innovation release, don’t blame Oracle about the lack of long term support. You should know what you are getting into.

Regardless of what your company is doing, still try and keep on top of the new features. The more you know now, the easier the transition will be when the new release does become available to you, whether you’re a developer, DBA or both.

You can play with Oracle database 21c, and future releases, from your Oracle Cloud Free Tier account. Free is a really good price. 🙂



Video : Long Identifiers in Oracle Database 12.2 Onward

In today’s video we’ll quickly demonstrate long identifiers, introduced in Oracle database 12.2.

The video is based on this article.

I was moved to make this because I saw someone on Twitter complaining about the 30 character limit in Oracle. I figured they were probably using an old version of the database, but that’s their fault. A short video was convenient, as I was working this weekend, and didn’t have much free time. 🙂

The star of today’s video is Marcelle Kaye, in honour of her recent birthday. Hopefully we will get to meet up again one day…



VirtualBox 6.1.18

VirtualBox 6.1.18 has been released.

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 problems.

I’ll be running new Packer builds for the oraclebase/oracle-7 and oraclebase/oracle-8 vagrant boxes, so they should appear with the new version of the guest additions over the next day or so.




Vagrant and Docker Builds: ORDS 20.4, SQLcl 20.4 and Database Patches

The January Oracle quarterly patches were released yesterday, which prompted me to do some new builds.

We got Oracle REST Data Services (ORDS) 20.4 and SQLcl 20.4, which I use in a number of my Vagrant and Docker builds, so I updated them and ran some builds.

The Vagrant database builds, which include ORDS, can be found here.

The Docker ORDS builds can be found here.

I also updated Tomcat to 9.0.41 and OpenJDK to 11.0.10_9 from AdoptOpenJDK.

Once I finished those I decided to try out the Oracle database 19c (19.10) OJVM+DB combo patch on a single instance build. That went fine. You can see that build here.

Since that went OK, I figured it was worth trying to update my OL8 19c RAC build with the 19.10 OJVM+GI combo patch. That also went fine. You can see that build there.

I wasn’t really expecting to cover so much ground so quickly, but that’s the great thing about automation. 🙂

Tomorrow I’ve got to start putting together all the patch scripts for work. It’s always a bit tedious because I have to deal with a lot more products and variations, and I have to make sure I don’t screw up. Happy patching… 🙂



PS. If you are interesting in ORDS, SQLcl, Vagrant or Docker, these might help.

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!