Making the most of Oracle 18c XE – Pluggable Databases and the Oracle Eco-System

This was going to be a simple post about creating multiple Pluggable Databases (PDBs) in Oracle 18cXE.
But you know what it’s like, you get your lego database set out and then you spot those unusual shaped bricks… and the transparent ones… oh, that one has a wheel on…and get a bit carried away.
What follows is a guide on how to create three concurrent PDBs on an Oracle 18cXE database. However, I have taken the opportunity to investigate other areas of the Express Edition platform and so will be making use of Advanced Compression as well as Oracle Enterprise Manager Express.
In fact, I wouldn’t be surprised if SQLDeveloper put in an appearance as well.
I’ve also included a couple of the more notable members of Oracle’s small-but-perfectly-formed eco-system into the final design. On top of all that, I’ll be performing a magic trick to ensure that I only have to install each piece of software once, even though it may end up in multiple PDBs…

No Data ? No Problem ! Fun and Games with the NO_DATA_FOUND exception

I can’t remember how long we’ve been in lockdown now but between you and me, I think my Oracle Database may be cracking up.
As Exhibit A, I present the fact that RDBMS seems to raise the NO_DATA_FOUND exception only when it feels like it…

Reading an OS file from inside an Oracle Database using an External Table

Two weeks into lockdown and some are looking to break the monotony by varying their diet…

For my part, I’ve decided to amuse myself by making a couple of “Top five” lists – the five best things about the lockdown and five things I’ve now finally got time to watch.

These lists are in text files are somewhere on my Database server and therefore provide the basis for the examples that follow.

When using Oracle, if you find yourself in a situation where you don’t have access to the Database Server file system, or would just rather use SQL to find what you’re looking for in a data set, you’re in luck.
The technique that used to be a neat way of viewing the database alert log from inside the database can now be re-purposed to look at any text file that’s located in a directory referenced by a Directory Object in the database.

What we’re going to look at here is :

• Setting up an external table for reading from files
• How to point the external table specific files and directories using EXTERNAL MODIFY
• How achieve the same thing in 11gR2 databases with the help of DBMS_LOCK

First of all though, just in case you’re wondering….

Putting VALIDATE_CONVERSION front and centre, or possibly center

I recently had an interesting conversation with Suzanne, a native of Pittsburgh PA, about how the Americans and British spell some words differently.
Adding some local colour ( color) to the discussion, Suzanne recounted the story of when the US Postal service, seeking to save money on printing, proposed removing “superfluous” characters from place names.
Well, the burghers of Pittsburgh weren’t having any of that thank-you-very-much and so the City’s name remains unabridged to this day. The denizens of Harrisburg weren’t so fortunate.
Whilst we may be separated by a common language, as programmers who work with Oracle, Suzanne and I do share the challenge of loading data into tables when the format of that data may not be entirely what we were expecting.
If you’re fortunate enough to inhabit the sunlit uplands of Oracle 12c R2 and beyond, we’re about to explore the shiny new VALIDATE_CONVERSION function which promises to make your life that little bit easier.
For those languishing on 11g, we’ll explore how we might implement a similar function in PL/SQL.

Oracle and Alternative Facts – generating test data with DBMS_RANDOM

There’s a bug in DBMS_RANDOM.VALUE. It consistently fails to return the correct set of lotter numbers.
On the plus side, it is surprisingly useful when you find yourself in a situation which is all too common for a Data Warehouse developer.
There’s a requirement for a new feed into your Warehouse from another system. You’ve managed to agree the file specification – what data will be included in the file, datatypes etc, but the developers working on the upstream system won’t be able to start providing test files for loading for weeks yet. Meanwhile, you need to start writing your code to ingest the feed.
Fortunately, you have all the tools available to :

• Generate lots of rows of test data
• Generate random values for strings, numbers and dates
• Use a set of pre-defined values randomly in your data generation

First of all, let’s take a look at…

SQLcl ALIAS – because you can’t remember everything.

I want to find out which file is going to hold any trace information generated by my database session. Unfortunately, I keep forgetting the query that I need to run to find out.
Fortunately I’m using SQLcl, which includes the ALIAS command.
What follows is a quick run-through of this command including :

• listing the aliases that are already set up in SQLcl
• displaying the code that an alias will execute
• creating your own alias interactively
• deleting an alias
• using files to manage custom aliases

Whilst I’m at it, I’ll create the alias for the code to find that pesky trace file too.

Great Football Managers and Oracle Unified Auditing

It’s quite a good time for English football at the moment. Not only have English clubs monopolised the finals of the two main European Club competitions this year, but Manchester City have made history by winning all three domestic competitions in the same season.
Note that this isn’t a British footballing first. Glasgow Rangers managed it way back in 1949. And whilst the European Cup ( Champions League if you must) has eluded City this season, Celtic managed that particular clean sweep in 1967.
In English football however, this particular treble is unprecedented. In fact, there are remarkably few managers who have been able to win every one of the major domestic honours in their entire career.
All of which will come in handy when looking for examples to illustrate the topic at hand, namely Oracle Unified Auditing.
With the aid of 18c Express Edition, we’ll be looking at :

• The Oracle supplied Unified Auditing Policies that are enabled by default
• Where to find the Audit Trail
• How to create our own Unified Auditing Policy to monitor DML operations on specific objects

ORA-01775 – Looping Chain of Synonyms and Epic Football Punditry

In 1990, Liverpool became English League Champions for the 10th time in 15 seasons.
Despite this impressive track record, my Dad came over all Yoda-esque and confidently predicted that they would not win The Title again in his lifetime.
Since then, Liverpool have won everything else, including the Champions League – OK Dad , the European Cup – but the prediction has held.
In fact, it’s gone on so long that it probably qualifies as a prophecy by now.
Before the start of each season, I can assess Liverpool’s prospects, by simply enquiring after his health.
“Musn’t grumble, ‘cos if you do no-one bloody listens !” can be taken as a synonym for “I’ll be around for a while yet, so don’t waste your money on backing Liverpool to win it this season”.
Which brings us to the subject of this post – namely the apparently random nature of the ORA-01775 error, where synonyms are concerned…

Oracle Create Schema – multiple DDL statements in a single transaction

I was going to begin with some extended Brexit metaphor to illustrate the chaos and confusion that can ensue when you first encounter Oracle’s CREATE SCHEMA command.
Fortunately, the Dutch Government saved me the trouble :

Much as I’d like to believe that the Cookie Monster has finally overcome his Sesame Street type casting, I can’t help noticing that the Brexit Monster never seems to in the same room as this guy…

In Oracle, the term “schema” is used interchangeably with the term “user”. Creating a user in Oracle automatically creates a schema of the same name belonging to that user.
The process is so seamless that it’s almost impossible to tell where one begins and the other ends.
You may therefore be somewhat confused the first time you encounter Oracle’s CREATE SCHEMA command…

Using a Proxy User for Code Deployment in Oracle

“Proxy Users !” exclaimed Debbie.
“I say, that’s rather harsh don’t you think ?” came the rather startled reply from her boss.
Debbie sighed. They were in the midst of a discussion on the subject of how best to deploy database changes to multiple schemas.
“I meant”, she replied with iron patience, “that we could set up a proxy user to connect as each application owner in turn. That way, we wouldn’t have to grant those CREATE ANY privileges that get auditors so worried”.
“Oh, I see”, said Mike, who didn’t.
Not for the first time, Debbie wondered whether she had been lumbered with a less competent man as her boss simply in order to imbue this post with a semblance of social realism.
“I think”, she said, “that it’s time to move on to the techie bit.”
Debbie is right, as usual…

In order to make a change in Oracle ( or any database for that matter), you need at some point to connect to the database and run some SQL.
This is relatively straightforward if you are using the schema that is – or will be – the owner of the objects you are creating or changing.

However, this may not be possible if the account is identified externally or – in more recent releases – it’s a schema only account.
So, what is the best way to setup and use an account to make such changes in other schemas ?