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”…
Continue reading

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

Continue reading

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:

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…
Continue reading

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…
Continue reading

Chasing your tail – with SQL*Plus and SQLcl

Do you remember the film Up where the dogs were always distracted as soon as anyone mentioned squirrels ?
Well, there I was, continuing my journey through the wonderful world of PL/SQL Unit Tests when suddenly, SQLcl !
Yes, Oracle have just released the first production version of SQLcl.
Since I first looked at an Early Adopter version of SQLcl there have been several enhancements. One of these, the REPEAT command, has the potential to implement functionality akin to the good old *nix tail -f command for Oracle Database tables.
It turns out that you may also be able to do something similar in SQL*Plus…
Continue reading

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

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…. Continue reading