Geeky Speed Freakery in the Wild (South) West…

After many happy years, we decided to move house.
Deb has always said she wants to live near the sea and, despite it’s many attractions, Milton Keynes is about as far inland as you can get on the UK mainland.
Initially, the idea of a new home conjured up rather different images for each of us.
Deb envisioned a lovely little thatched cottage complete with an open fire around which to warm ourselves on those cold winter evenings.
I wanted a hollowed out volcano.
Eventually we compromised. Deb accepted that her ideal may present something of a fire hazard and, to be fair, so did mine.
That’s how we found ourselves looking around a newly built house in North Devon.
Deb was immediately impressed with the space, the potential…and all those other things that those daytime property programs say you should look for.
I was equally impressed, but for a very different reasons. I had noticed a little white box on the wall in the living room. When questioned, the Sales Advisor had uttered the magic words “Fibre to the Premises”.
Being a mere 5 minutes away from the Atlantic Highway was just an added bonus.

Needless to say we’d found our new home.

Continue reading


ORA-06592 and the Case of the Happy Australians

Another Ashes Tour to Australia has come and gone and the home team once again hold The Urn.
For any non-cricket fans, I should probably explain.
Every four years, England sends their Men’s and Women’s Cricket Teams to Australia on a goodwill mission.
The object of the exercise is to make Australians feel good about their country as their teams inevitably triumph.

These recently concluded contests provide the theme for the illustration of the less-than-straightforward circumstance surrounding the ORA-06592 error which follows.
When encountering this error, you’ll probably see something like

ORA-06592: CASE not found while executing CASE statement

06592. 00000 -  "CASE not found while executing CASE statement"
*Cause:    A CASE statement must either list all possible cases or have an
           else clause.
*Action:   Add all missing cases or an else clause.

Despite this apparently definitive advice, you don’t always need to cover any possible case, or include an ELSE clause… Continue reading

Private Functions and ACCESSIBLE BY Packages in 12c

My recent post about PLS-00231 prompted an entirely reasonable question from Andrew :

“OK so the obvious question why [can’t you reference a private function in SQL] and doesn’t that defeat the objective of having it as a private function, and if so what about other ways of achieving the same goal ?”

I’ll be honest – that particular post was really just a note to self. I tend to write package members as public initially so that I can test them by calling them directly.
Once I’ve finished coding the package, I’ll then go through and make all of the helper package members private. My note was simply to remind myself that the PLS-00231 error when compiling a package usually means that I’ve referenced a function in a SQL statement and then made it private.

So, we know that a PL/SQL function can only be called in a SQL statement if it’s a schema level object or it’s definied in the package header because that’s the definition of a Public function in PL/SQL. Or at least it was…

In formulating an answer to Andrew’s question, it became apparent that the nature of Private functions have evolved a bit in 12c.

So, what I’m going to look at here is :

  • What are Private and Public package members in PL/SQL and why you might want to keep a package member private
  • How 12c language features change our definition of private and public in terms of PL/SQL objects
  • Hopefully provide some up-to-date answers for Andrew

Continue reading

Cloning around with VirtualBox

I’m in a slight trough of a week at the moment.
The excitement of seeing what Santa brought has begun to fade but I’ve yet to summon the requisite enthusiasm for seeing in the New Year.
So this post is really one of those little “Notes to self” so that I can save myself some time when next I need to spin up an Oracle database on a VirtualBox VM…
Continue reading

PLS-00231 – The best laid (cunning) plans and private package functions

There are times when I feel like Baldrick.
One moment I’m all boundless optimism and cunning plans and the next, I’m so confused I don’t know what my name is or where I live.
One such recent bout of existential uncertainty was caused by the error mentioned in the title of this post, or to give it it’s full name :

PLS-00231 : Function <function name> may not be used in SQL

So, from the beginning…

Continue reading

The Rise of AI – A Cautionary Halloween Tale

According to the hype doing the rounds at the moment, we’re all about to be replaced by Robots and/or Artificial Intelligence.
Alexa, Cortana, Siri, we already have computers that are talking to us.
Incidentally, all of these devices seem to have female voices, even the gender-neutrally named Google Assistant.
Deb reckons that this is because everyone understands that, when it comes down to it, women know best.
Whilst I wouldn’t presume to question this assertion, I must confess to being somewhat unconvinced as to the inevitability that AI is about to take over the world.
For a start, there are those automated checkouts that have been in supermarkets for several years now.
Not only are they all rather temperamental, they all have their own individual quirks.
Then there are the Sat-Navs they’re incorporating into cars. What with one thing and another I seem to be spending an increasing amount of my life arguing with other programmers’ bugs…
Continue reading

Importing an Excel Spreadsheet into an Oracle database with SQLDeveloper

It was late. In a corner of the Treasury however, a light burned brightly.
Phillip Hammond, Chancellor of the Exchequer, was still working.
“Spreadsheet Phil” was trying to formulate a plan to rectify the nation’s finances in the event that the Prime Minister was won over by the ebullient Mr Johnson and decided upon a “No Deal” Brexit.
Boris Johnson was currently based at the F.O. which, Phil considered darkly, was somewhat appropriate.
If only his predecessor had had the foresight to stick a couple of billion on Liecester City to win the league at 5000 to 1, The Country’s finances would be in much better shape.
In the midst of this gloomy reflection, he was struck by a sudden inspiration. The crowded half-hour that followed resulted in – what else – a spreadsheet.
The information therein could save The Economy. “Hmmm”, thought Phil, “this stuff is far too important to leave in Excel, I really need to put it into my Oracle database. I wonder if SQLDeveloper could help”… Continue reading