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

Having a mid-life crisis on top-of-the-range hardware

I’ve recently begun to realise that I’m not going to live forever.
“Surely not”, you’re thinking, “look at that young scamp in the profile picture, he’s not old enough to be having a mid-life crisis”.

Well, five minutes ago, that was a recent picture. Suddenly, it’s more than 10 years old. As Terry Pratchett once observed, “Inside every old person is a young person wondering what happened”.

Fortunately, with age comes wisdom…or a sufficiently good credit rating with which to be properly self-indulgent.
Now, from what I’ve observed, men who get to my stage in life seem to seek some rather fast machinery as a cure for the onset of morbid reflections on the nature of their own mortality.
In this case however, it’s not the lure of a fast car that I’ve succumbed to. First and foremost, I am a geek. And right now, I’m a geek with a budget.

Kicking the habit of WM_CONCAT for a delimited list of rows, with LISTAGG

I gave up smoking recently.
Among other bad habits that I need to kick is using the (not so) trusty WM_CONCAT.

The Rest of the Django App – the View and Controller Tiers

As is the way of Software Projects, I’m starting to get a bit of pressure from the customer about delivery.
As is slightly less usual in such circumstances, the question I’m being asked is “when are you going to get out there and mow that lawn ?”
Fortunately, Django is “for perfectionists with deadlines” …or minions with gardening chores waiting (probably) so I’d better crack on.

Now, I could do with some assistance. Fortunately, these guys will be around to help :

Pay bananas, get minions.

In case you haven’t been following the story to date, this project is to create an Application to allow my better half to look at which movies we have on DVD or Blu-Ray.

So far my Django journey has consisted of :

• Setting it up to run on Apache on a Raspberry Pi
• Taking a closer look at the database objects that Django generates

Django follows the Model-View-Controller (MVC) pattern of application design. Having spent some time looking at the Database (Model) layer, we’re now going to turn our attention to the View (what the end-user sees) and the Controller ( the application logic that makes the application work).

The Django Fandango Farrago – Looking at Django’s Physical Data Model Design

I’m sure I’m not the only Oracle Developer who, over the years, has conjured a similar mental image during a planning meeting for a new web-based application…

…and we’re going to use an ORM

If you want the full gory details as to why this is so troubling from an Oracle database perspective, it is a topic I have covered at length previously.

This time, however, things are different.
Yes, I am somewhat limited in my choice of database due to the hardware my application will run on (Raspberry Pi).
Yes, Django is a logical choice for a framework as I’m developing in Python.
But, here’s the thing, I plan to do a bit of an audit of the database code that Django spits out.
< obligatory-Monty-Python-reference >That’s right Django, No-one expects the Spanish Inquisition ! < obligatory-Monty-Python-reference / >

Donde esta el Base de datos ?!

I know, this is a character from Blackadder and not Monty Python, but I’ve often regretted the fact that there never seems to be a vat of warm marmalade around (or some kind of gardening implement for that matter), when you enter those all important application architecture discussions at the start of a project.

As a result, one or two further Blackadder references may have crept in to the remainder of this post…

Configuring Django with Apache on a Raspberry Pi

Deb has another job for me to do around the house.
She would like to have a means of looking up which Films/TV Series we have lying around on Blu-Ray or DVD so she can save time looking for films we haven’t actually got. Just to be clear, she doesn’t mind hunting around for the disc in question, she just wants to make sure that it’s somewhere to be found in the first place.
She wants to be able to do this on any device at any time (let’s face it, there’s even a browser on your telly these days).
As DIY jobs go, this is a long way from being the worst as far as I’m concerned. After all, this time I should be able to put something together without the potential for carnage that’s usually attendant when I reach for the toolbox.

I happen to have a Raspberry Pi lying around which should serve as the perfect hardware platform for this sort of low traffic, low data-volume application.
The Pi is running Raspbian Jessie.
Therefore, Python is the obvious choice of programming language to use. By extension therefore, Django appears to be a rather appropriate framework.
In order to store the details of each movie we have, we’ll need a database. Django uses with Sqlite as the default.

We’ll also need an HTTP server. Whilst Django has it’s own built-in “development” server for playing around with, the favoured production http server appears to be Apache.

Now, getting Django and Apache to talk to each other seems to get a bit fiddly in places so what follows is a description of the steps I took to get this working…leaving out all the bits where I hammered my thumb…

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