Dude, Where’s My File ? Finding External Table Files in the midst of (another) General Election

It’s early summer in the UK, which means it must be time for an epoch defining vote of some kind. No, I’m not talking about Britain’s Got Talent.
Having promised that there wouldn’t be another General Election until 2020, our political classes have now decided that they can’t go any longer without asking us what we think. Again.
Try as I might, it may not be possible to prevent the ear-worm phrases from the current campaign slipping into this post.
What I want to look at is how you can persuade Oracle to tell you the location on disk of any files associated with a given external table.
Specifically, I’ll be covering :

• getting the name of the Database Server
• finding the fully qualified path of the datafile the external table is pointing to
• finding other files associated with the table, such as logfiles

In the course of this, we’ll be challenging the orthodoxy of Western Capitalism “If You Can Do It In SQL…” with the principle of DRY ( Don’t Repeat Yourself).
Hopefully I’ll be able to come up with a solution that is “Strong and Stable” and yet at the same time “Works For The Many, Not the Few”…

Breaking the Rules – why sometimes it’s OK to have a standalone PL/SQL Function

It was late. We were snuggled up on the sofa, watching a Romcom and debating whether to go to bed or see it through to the bitter( well, sickly sweet) end.

Wearily, I made the point that in the end the film would follow Heigl’s Iron Law of Romcom which can be summarised as “Katherine always gets her man”.

Deb begged to differ. Her argument was that, for every Colin Firth, riding into the sunset with his Bridget Jones, there’s a poor( largely blameless) Patrick Dempsey whose immediate future includes long-evenings alone in front of the telly and shopping for microwave meals for one.
The point is that even the most rigid rules tend to have their exceptions.

The star of this post is the oft-quoted rule that PL/SQL program units should always be incorporated into a Package.
There are special cameo appearances by “Never use Public Synonyms” and the ever popular “Never grant privileges to Public”.

Automated Testing Frameworks and General Rule-Breaking in PL/SQL

If there’s one thing that 2016 has taught us is that rules (and in some cases, rulers) are made for breaking. Oh, and that it’s worth putting a fiver on when you see odds of 5000-1 on Leicester winning the League.

Having lacked the foresight to benefit from that last lesson, I’ve spent several months looking at Unit Testing frameworks for PL/SQL. In the course of this odyssey I’ve covered:

• Writing tests without using a framework
• SQLDeveloper Unit Testing
• utPLSQL
• Ruby-plsql-spec

This post is a summary of what I’ve learned from this exercise, starting with the fact that many of the rules we follow about good programming practice are wrong…

Post-Truth PL/SQL

We’re living in a Post-truth age. I know this because I read it in my Fake News Feed.
Taking advantage of this, I’ve updated the definition of PL/SQL.
Up until now, it would be true to say that PL/SQL is a 3GL based on ADA that’s incorporated into the Oracle RDBMS.
Post truth, the definition is that PL/SQL is a 3GL that comes with it’s own built-in Oracle RDBMS.

By a stroke of good fortune, my son recently bought me a copy of Ghost in the Wires by Kevin Mitnick and William L Simon, which begins each chapter with an encrypted phrase.
If your anything like me, you’d spend a fair amount of time geeking over this sort of problem, most likely using some fashionable programming language to help solve the riddles with which you were presented.

In my house at least, PL/SQL is back in fashion…

Testing Times – using ruby-plsql-spec for testing PL/SQL

There is method in the madness. It’s now clear that Donald Trump’s reluctance to commit to the Paris Climate Change Accord is because US methane emissions have been hugely under estimated. Yes, it turns out that there are many more Shy Trumpers in America than (almost) anyone expected.
Meanwhile, back in the UK we know that Brexit means Brexit but we still don’t know what Brexit means.
In amongst the chaos, UKIP have decided to take a fresh approach to the business of selecting a leader. This time, they’re staging a Cage Match.

Taking a leaf out of UKIP’s book I’ve decided to take a slightly unusual approach to Unit Testing my PL/SQL code.
Having looked at the SQLDeveloper Unit Testing Tool and utPLSQL, both of which utilise the database to persist objects, this time, I’m taking a look at a framework which takes a rather less database-centric approach, namely ruby-plsql-spec.

What I’ll be looking at is :

• Installation of the framework and required components
• A quick recap of the application being tested
• Writing and executing Unit Tests
• Summary and conclusions

utPLSQL – We’re building the…Unit Tests!

You’ll be relieved to hear that I’m not going to talk about Brexit any more. It’s done and finished and it’s time everyone moved on.
Besides, from my current vantage point ( a small island off the coast of Europe), other people have their own problems.
In the USA for example, the nation is currently engrossed in what appears to be an exercise to find the second most unpopular person in the country…and make them President.
If the subject of this post were a Presidential Candidate, it would probably be nicknamed “The Comeback Kid”.
No, Bill Clinton hasn’t released a PL/SQL testing framework, but Steven Feuerstein has and – contrary to what you may have heard – utPLSQL is very much alive and kicking.
As the first Test framework written specifically for PL/SQL, utPLSQL is the perfect subject for my continuing testing odyssey.

To date, this has included

• a brief look at hand-crafting Unit Tests for PL/SQL
• a dive into the nooks and crannies of SQLDeveloper Unit Testing

The political analogy is rather apposite when you consider that using utPLSQL largely revolves around making a number of assertions…which may or may not be true.

What we’ll be looking at is :

• Installing the utPLSQL framework
• Your first Test and how to run it
• A quick recap of the Footie Application we’re testing with
• The utPLSQL Assert API
• Building and executing Test Suites
• Retrieving utPLSQL test execution results programmatically

But first….

(Almost) Everything you ever wanted to know about SQLDeveloper Unit Testing but were afraid to ask

The Political fallout from Brexit continues unabated.
In the immediate aftermath of the vote, David Cameron resigned as Prime Minister, triggering a power-struggle to succeed him.
Secret pacts, double-crosses and political “assassination” followed in short order.
It was like watching an episode of Game of Thrones although, mercifully, without the full-frontal nudity.
As for the Labour Party, they have captured the Zeitgeist…by descending into the sort of internal conflict for which the word “internecine” was invented.

Following the current trend that promises are made for breaking, this post has arrived a little bit later than advertised.
I write most of this stuff on the train to and from work, and, as they have been unusually punctual of late, my Sprint 1 velocity is somewhat lower than anticipated.
So, with apologies for my tardiness…