Video : DBMS_CLOUD : External Tables

In today’s video we’ll demonstrate the functionality of the DBMS_CLOUD package, with specific reference to external tables based on objects in a cloud object store.

The video is based on a section in this article.

You may find these useful.

The star of today’s video is Alex Zaballa, who has got to be one of the most certified people I’ve ever met. 🙂

Cheers

Tim…

Oracle Database World : 3 Virtual Events Around the World

Just a quick reminder there are some Oracle Database World events happening in three geographical regions over the next few months. Here’s the marketing stuff.

These 2-day virtual conferences will run in three regions:

  • Americas – October 26th & 27th
  • EMEA – November 2nd & 3rd
  • JAPAC – December 1st & 2nd

They will be delivering ~approximately 90 sessions and 24 hands-on labs (powered by Oracle LiveLabs).

Register for your region here!

Cheers

Tim…

Video : DBMS_CLOUD : Objects and Files

In today’s video we’ll demonstrate the functionality of the DBMS_CLOUD package, with specific reference to objects in a cloud object store and files on the database server file system.

The video is based on part of this article.

You might find these useful also.

The star of today’s video is Roel Hartman, who used to do APEX, but now just runs marathons I think… 🙂

Cheers

Tim…

Joel Kallman Day : It’s a Wrap (#JoelKallmanDay)

Yesterday was an Oracle community day called the Joel Kallman Day (#JoelKallmanDay) 2021.

I would like to say a big thank you to everyone who took the time to join in. Here is the list of posts I saw in chronological order. If I missed you out, give me a shout and I’ll add you. 🙂

There are a few non-English posts in there, which I always like to see. You can Google Translate them to check them out. The community is not all about English speakers.

In addition to the blog posts there were tweets and a bunch of people joining in to retweet, which makes the day have more impact. You can see what happened on Twitter here. Thanks everyone for getting involved and see you next year for #JoelKallmanDay 2022!

Cheers

Tim…

My APEX History #JoelKallmanDay

For my entry in the Joel Kallman Day (#JoelKallmanDay) I thought I would run through my history of using Oracle Application Express (APEX).

I was aware of Web DB, but I never used it. I have this vague memory of taking a look at it, but that was all.

I remember Tom Kyte mentioning Flows and later Project Marvel, and being a Tom Kyte fanboy it sparked my interest. At that point the tool felt kind-of awkward to use, and since I had no pressing need for it, it got relegated to an idle curiosity for me. I would have a play, then ignore it for months. I kept my eye on it during the HTML DB and early APEX days, but it was still not something I used regularly. Typically what would happen is I would need to do something, I would “re-learn” APEX to do it, finish that task, then forget about it for months. The next time I needed APEX, it would often be a new version, so I would “re-learn” it again…

In 2009 I went to ODTUG in Monterey, and there was an APEX symposium as part of the conference, so I made it my mission to pick APEX sessions as much as possible. It was good to connect with some of the folks in the APEX community. Even back then it was one of the most dynamic Oracle communities. I knew a few of the folks already through meeting at other conferences, but it was fun to immerse myself in APEX for a while.

After that conference I slipped back into the habit of re-learn it, forget it, relearn it. I was constantly aware of it, and able to do relatively simple developments, but I never focused on it long enough to get good. Remember, my job didn’t involve that type of development, so it was hard to spend too much time on it.

Since joining my current company in 2012 I’ve used APEX a lot more. It started off with me writing little PL/SQL utilities for people and giving them a front end using APEX. Things like reports on interface staging tables, screens to manage database users and some simple CRUD apps. At that point APEX was kind-of underground in the company. There was only really me using it. As time went on I kept pushing it and gradually it started to pick up some momentum. We now have a few people that do APEX development as part of their role. At this point I’m relegated to managing the infrastructure behind it, and problem solving when people hit an issue. I’ve seen and heard so much over the years that it’s often easier for me to solve certain problems. Also, I know a lot of good people who can help. 🙂

I have this joke that I’ve got more than 15 years “experience” of APEX, but I’m the worlds worst APEX developer. Despite that, I know enough to know APEX is probably the most productive way to develop applications against an Oracle database. The “Low Code” nature of it is great for people with less experience of database development, but it’s also a great way to make skilled developers super productive!

I’ve written a small number of articles about APEX over the years, as well as some surrounding technologues.

So there you have it. My life as the worlds worst APEX developer on the planet. 🙂

Cheers

Tim…

Joel Kallman Day : Announcement

Since 2016 we’ve had an Oracle community day where we push out content on the same day to try and get a bit of a community buzz. The name has changed over the years, but after speaking to some folks at Oracle I decided to rename it permanently to the “Joel Kallman Day”. Joel was big on community, and it seems like a fitting tribute to him.

When is it?

The date is Monday October 11th. That’s a week from today!

How do I get involved?

Here is the way it works.

  • Write a blog post. The title should be in the format “<insert-the-title-here> #JoelKallmanDay“.
  • The content can be pretty much anything. See the section below.
  • Tweet out the blog post using the hashtag #JoelKallmanDay.
  • Publishing the posts on the same day allows us to generate a buzz. In previous years loads of people were on twitter retweeting, making it even bigger. The community is spread around the world, so the posts will be released over a 24 hour period.
  • Oracle employees are welcome to join in. This is a community day about anything to do with the Oracle community.

Like previous years, it would be really nice if we could get a bunch of first-timers involved, but it’s also an opportunity to see existing folks blog for the first time in ages! 

The following day I write a summary post that includes links to all the posts that were pushed out through the day. You can see examples here.

What Should I Write About?

Whatever you want to write about. Here are some suggestions that might help you.

  • My favourite feature of {the Oracle-related tech you work on}.
  • What is the next thing on your list to learn.
  • Horror stories. My biggest screw up, and how I fixed it.
  • How the cloud has affected my job.
  • What I get out of the Oracle Community.
  • What feature I would love to see added to {the Oracle-related tech you work on}.
  • The project I worked on that I’m the most proud of. (Related to Oracle tech of course)

It’s not limited to these. You can literally write about anything Oracle-related. The posts can be short, which makes it easy for new people to get involved. If you do want to write about something technical, that’s fine. You can also write a simple overview post and link to more detailed posts on a subject if you like. In the previous years the posts I enjoyed the most were those that showed the human side of things, but that’s just me. Do whatever you like. 

So you have a week to get something ready!

Cheers

Tim…

Video : Oracle Database 21c Express Edition (XE) Installation

In today’s video we’ll demonstrate how to install Oracle Express Edition 21c on Oracle Linux 8.

The video is based on this article.

Here are some other things you might find useful.

The star of today’s video is Martin Widlake, doing his best Monty Python impression.

Cheers

Tim…

Oracle Database 21c Express Edition (XE) : Article and Vagrant Builds

You probably saw a stream of Tweets yesterday mentioning the release of Oracle database 21c Express Edition (XE) for Linux. It’s a very important release as it’s totally free to use.

Of course I had a play as soon as I got hold of it. If you’ve used one of the RPM installations of Oracle before, there shouldn’t be any surprises. The approach for the XE RPM installation is similar to that of the Enterprise Edition RPM installation. Here’s a quick run through.

I also added a couple of Vagrant builds for Oracle 21c XE on Oracle Linux 7 and 8.

I suspect this release has made a lot of people very happy! 🙂

Cheers

Tim…

Data Pump Enhancements in Oracle 21c (and a little support story)

I’ve been having a play with some of the Oracle 21c data pump enhancements. This post links to the resulting articles, and includes a little story about one of the features.

The Articles

Here are the articles I wrote during this investigation.

As an aside, I also knocked up a quick overview of the DBMS_CLOUD package. I’ve used many of the routines in this package in my autonomous database articles over the last few years, but I got a bit sick of jumping around to get the syntax of different operations, so it seemed sensible to have a centralised description of everything, along with working examples.

The Story

The article about using expdp and impdp with a cloud object store (Oracle Cloud Object Storage, AWS S3 or Azure Block Storage) came with a little bit of drama.

Back in the 18c days it was possible to import into an Autonomous Database using a dump file on a cloud object store using the 18c impdp utility. I wrote about this at the time (here). At that time export to a cloud object store using the expdp utility was not supported, and the import wasn’t supported with an on-prem database.

Oracle 21c introduced the ability to export from an Autonomous Database to a cloud object store, which worked fine first time. The documentation also listed a new feature called, “Oracle Data Pump Supports Export to and Import From Cloud Object Stores“. This sounded very much like it meant for on-prem databases, and sure enough it did.

When I started trying to use this feature I pretty quickly hit a road block. The expdp utility couldn’t connect to the object store bucket. I raised a call with Oracle Support about it. While I was waiting for a response I figured this functionality may have a dependency on the DBMS_CLOUD package under the hood, so I installed it in my on-prem database. The on-prem installation of DBMS_CLOUD was working OK, but the expdp utility was still failing to contact the object store bucket.

Due in part to friends in high places, my SR got picked up and it was confirmed the DBMS_CLOUD installation was an undocumented prerequisite, but it was still not working for me. The support engineer confirmed they could replicate the issue too. A few interactions between support and development resulted in bug 33323028, which fortunately had a simple workaround. At that point the support engineer was up and running, but I still had a problem. A bit of tracing later and it turned out my remaining issue was PEBCAK (Problem Exists Between Chair And Keyboard)…

When I installed the DBMS_CLOUD package it said to put a wallet reference in the sqlnet.ora file. I did that and the package seemed to be working OK, so I thought everything was good. Unfortunately I put it under the ORACLE_HOME and Oracle 21c uses a read-only Oracle home, so that’s the wrong place. It didn’t affect the package, as that picks up the wallet location from a database property, but it affected the expdp and impdp utilities. I keep telling people read-only Oracle homes will trip you up if you are not used to them, and sure enough it tripped me up. Damn you muscle memory! Once the correct sqlnet.ora file was amended everything was good.

So the journey to get this feature working involved:

  • An undocumented prerequisite, which I guessed.
  • A bug which Oracle Support and the dev folks gave me a workaround to.
  • An idiot (me) trying to learn not to be an idiot.

With a bit of luck the bug fix/workaround will be rolled into a future release update, so you may never see this. The MOS note about the DBMS_CLOUD package installation suggests this might also be part of the database by default in future. That would be great if it happens.

Anyway, after that little drama I was able to export data from my on-prem database to a dump file located on a cloud object store, and import data from a cloud object store into my on-prem database. Happy days!

Thanks to the support and dev folks who helped get me through this! 🙂

By the way, all the other Oracle 21c data pump new features worked without any issues.

So there you have it. Some new articles and a little bit of drama… 🙂

Cheers

Tim…