Read Only Access for providing backend support for an Oracle Application

The World Cup is finally over and “It’s Coming Home !”
For quite a long time, we English laboured under the illusion that “it” was football.
Fortunately for Scots everywhere, “It” turned out to be the World Cup which, like so many international sporting competitions, was conceived in France.

Another area that is often subject to flawed assumptions is what privileges are required to provide read-only access for someone to provide support to an Oracle Application.
So, for any passing auditors who may be wondering why “read only” access to an Oracle application sometimes means Write, or even Execute on certain objects…

Advertisements

You have chosen not to trust… – Citrix Receiver and SSL error 61 on Ubuntu

After months of trouble-free operation, Citrix Receiver decided to wreak some havoc one morning last week.
Connecting to work (using Firefox on Ubuntu and Citrix Receiver for Linux 13.8) was trouble free as usual.
However, when I then tried to select a PC to remote into, Citrix informed me that …

“You have chosen not to trust Entrust Root Certification Authority – G2. SSL error 61”

At that point, I reflected that what I knew about Citrix and SSL certificates would fit on the back of a fag packet.
After some intensive “research” it should now fit into a short blog post…

First Steps in SQLDeveloper Data Modeler

It’s true, Oracle are giving away free stuff. “Oracle ?”, I hear you say, “as in Larry’s Database Emporium and Cloud base ?” The very same.
It’s been going on for quite a while and includes relatively hidden gems such as SQLDeveloper Data Modeler.

There is some confusion around this particular tool for a couple of reasons.
When it was first released (sometime around 2009 as I recall), Data Modeler was an additional cost option. However, that didn’t last long.
At present (and for a number of years now), it is available either as a completely standalone tool, or as a fully integrated component of the SQLDeveloper IDE.
Either way, it costs exactly the same as the SQLDeveloper IDE – i.e. nothing.

I can tell you like the price, want to take it for a spin ?

I’m going to focus here on using the integrated version of Data Modeler. This is because

• I want to use it for small-scale modelling of the type you might expect to find when using an Agile Methodology
• I’m a developer and don’t want to leave the comfort of my IDE if I don’t need to

What I’m going to cover is :

• Viewing a Table Relationship Diagram (TRD) for an existing database table
• Creating a Logical Data Model and Entity Relationship Diagram (ERD)
• Generating a physical model from a logical model
• Generating DDL from a Physical Model (including some scripting tweaks to suit your needs)
• Using a Reporting Schema and pre-canned SQLDeveloper Reports to explore your models

Disclaimer
This post is about introducing the features of Data Modeler in the hope that you may find them useful.
It’s not intended as a paragon of data modelling virtue.
Come to that, it’s not intended as a definitive guide on how to use this tool. I’m no expert with Data Modeler (as you are about to find out). Fortunately, there are people out there who are.
If, after reading this, you want to explore further, then you could do worse than checking out words of Data Modeler wisdom from :

• Heli Helskyaho
• Jeff Smith

Let’s get started…

utPLSQL 3.0 – How to have your cake and eat it

“You can’t have your cake and eat it !” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations.
They also seem to be a bit intolerant of “cherry picking”.
I’ve never really understood the saying, “You can’t have your cake and eat it”.
What’s the point in having the cake unless you are going to eat it ?
Fortunately, I’m not alone in my perplexity – just ask any Brexiteer member of the British Cabinet.
For those who want to make sense of it ( the saying, not Brexit), there is a handy Wikepedia page that explains all.

When it comes to Unit Testing frameworks for PL/SQL, compromise between cake ownership and consumption is usually required.
Both utPLSQL 2.0 and ruby-plsql-spec have their good points, as well as some shortcomings.
Of course, if you want a more declarative approach to writing Unit Tests, you can always use TOAD or SQLDeveloper’s built-in tools.

Recently, a new player has arrived on the PL/SQL testing scene.
Despite it’s name, utPLSQL 3.0 appears to be less an evolution of utPLSQL 2.0 as a new framework all of it’s own.
What I’m going to do here, is put utPLSQL 3.0 through it’s paces and see how it measures up to the other solutions I’ve looked at previously.
Be warned, there may be crumbs…

Streaming Videos with Plex on Raspberry Pi

The recent Bank Holiday weekend in England provided me with a perfect opportunity to get on with some D.I.Y.

We have a collection of movie files, which I’ve stored on an external USB hard-drive. At the moment, these files are only accessible from the smart TV it’s plugged into.
I want to be able to stream these movies to the various Connected Devices we have around the house.

Time, once again, to call on my trusty Raspberry Pi 2 b-spec, running on Raspbian Jessie.

What I’m going to do is :

• Mount my USB Drive on the Pi
• Install Plex Server on the Pi to facilitate streaming
• Install Plex Client on relevant Connected Devices
• Create a Library containing all of my movies
• Stream a movie whilst I wait for it to stop raining

Hopefully after all that, I’ll be looking at something like this :

Before we do any of that however, we need to get the USB drive to work on the Pi…

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.

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…