Category Archives: Oracle

Post navigation

Deb’s been recovering from an operation recently. During her convalescence, I have been designated as her nurse and carer.
Careful planning was required prior to the op. She promised not to over do things, and I promised to attend to her every need.
“I should have a little bell so I can ring when I need you”, she opined. “After all, that’s what they do in Downton Abbey”.

We don’t have a bell. Fortunately Deb did have something that would do the job. Last Christmas, a thoughtful relative had given her a toy gun, a replica of the sort that was in the Despicable Me films.

Yes, when my presence was required, Deb simply had to fire the Fart Gun.

In order to attempt to retain a little of the Downton aura that she had been so keen to capture, I did make a point of saying “You rang M’Lady”, in my best Parker-from-Thunderbirds voice whenever I was summoned by the sound of electronic flatulence.

It seems to have worked out OK in the end. Deb is now up and about, having survived a week of my cooking.

When not attending to my nursing duties, I did have the chance to catch up with the unfolding story about the latest TalkTalk cyber attack.
Things that, in retrospect, are quite obvious were rather less clear at the time they were reported.

To begin with, the report was of a Distributed Denial of Service (Ddos) attack which had resulted in a loss of customer data.
Was this some fiendishly clever variation on the theme of a Ddos attack ? As far as I knew, such an exploit was designed solely to take down a site, not to obtain data.
Further “clarification” followed. It was reported that there had also been a “Sequential Attack”. I’ve never heard of one of those.
Just before I ran off to do some research, this was finally translated into techie – it was actually a SQL Injection (SQLi) attack.

Whilst it’s easy to laugh at the efforts of a CEO struggling with the terminology around this topic, it’s worth bearing in mind that the responsibility, ultimately, lies within the IT Department. But where ? with the Network Admins, Architects, the DBAs ?

As a Database Developer, you may well be feeling confident about security at this point.
After all, your code is sitting on the database server. Access to that server is probably restricted to a White List of machines, which will include the Application Server.
If you’re working with a public facing web application, the Application Server will be sitting behind a firewall.
Additionally, it may well be the case that the Application has been designed to implement the Data Access Layer pattern. Any database calls from the Controller layer are made to your PL/SQL packages which have been written to the Transactional API (XAPI) pattern. So, you don’t even need to wonder whether your Web Developers have used prepared statement calls.
On top of that, the application only connects to the database as a minimally privileged database user. The Application Owning schema itself is actually locked.

What we’re going to cover here is a Database Developer’s eye view of how such an application might look.
We’ll also look at just why these multiple layers of security provide no protection whatsoever against a SQL Injection attack.

Dependencies between a table and database stored program units can be found in the DBA_DEPENDENCIES dictionary view. However, this only records the fact that there is a dependency.
Say you have a long-running report that will benefit from an index on a table. It would be good to know if there’s a packaged procedure that does a large update on that table and may therefore take longer with a new index to populate. How can you figure out if there are any such procedures ?
Well, you’re in luck.

CRUDO is not, as you may think, a domestic cleaning product. It is, in fact, a PL/SQL application for generating CRUD matrices for Stored Program Units against the tables that they reference.
I’ve been playing around with something like this for a while now, andthis time, I’ve posted the resulting code on GitHub.

CRUDO enables you to :

Determine a CRUD Matrix for each database stored program unit against a given table ( or synonym on that table)

generate Matrices for all tables in a schema

record the results in a table for fast querying

update existing records only for program units that have been changed since the last update of it’s record

specify override records for situations where the dependency may not be recorded in DBA_DEPENDENCIES(e.g. when using dynamic SQL statements)

view runtime information on what it is doing

amend logging levels to provide more detailed information on large runs

laugh at my inability to come up with a decent name for it

I developed CRUDO on Oracle 11gR2 and it will run on any Oracle Database Edition ( XE, Standard One, Standard, Enterprise).

However well travelled you may be, after a while, you’re likely to conclude that there’s no taste like home.
Obviously, the taste in question will depend entirely on where it is you call home.
Being the native of a country that’s only discovered culinary excellence in the last 20 years or so, my tastes of home may be somewhat surprising to a non-native of the UK.
Chip shop chips, that goes without saying…although Belgians may take issue with any attempt to claim “Frites” as being a British dish.
Then there are the other “British” staples – a nice Lamb Kourma, or possibly even Sweet and Sour Chicken Hong Kong style.
It’s probably much simpler if your a Pole. To you home may well be Pierogi, the traditional Polish dumplings, usually containing a savoury filling but which can also hold something sweet.

Whatever it is, that taste of home is something familiar and reliable, rather like the ever-reliable sequence.NEXTVAL in Oracle… Continue reading →

Several years ago, the BBC found itself a little financially embarrassed. Appropriately enough, given the topic at hand, it was a bit strapped for cache…er…cash.

In an attempt to reduce expenditure, the decision was taken to re-broadcast the entire four series of Blackadder.
Happily, this guaranteed must-see viewing every Friday evening for about six months.

Possibly as a result of this, the dialogue from the show became something of a lingua franca within the IT community, and probably still is for geeks of a certain age.

Just think, how often have you be presented with “a cunning plan” or maybe found yourself in a situation that was “sticker than sticky the stick insect stuck on a sticky bun”.

Oh, just me then.

It is with Wisdom of the Ancients distilled through the dialogue from this classic show that we will now explore the wacky world of Sequence Caching in Oracle.
What’s that ? You don’t see anything wacky about it ? Hmmm, let’s take a closer look then…Continue reading →

Australian readers will be pleased to note that, despite the Antipodean flavour of this post, there will be no mention of The Ashes. It is a well known fact that Kangaroos are not interested in cricket.

My brother used to run a motorcycling school. One of his teaching techniques, out on the road was to say things like “What’s that Skippy ? Mike’s left his indicator on after the turn ?”
This is in reference to Skippy the Bush Kangaroo – a children’s TV program about the adventures of the eponymous hero with and uncanny knack of communicating life-threatening situations to humans, simply by means of a few tongue-clicking sounds.
My son spent quite a bit of time with his Uncle Steve.
Uncle Steve had quite a bit of influence on said child.
As a result, I’d often be on the receiving end of the distilled wisdom of Skippy…
“What’s that Skippy ? Dad’s left his keys on the table ?”
“What’s that Skippy ? Dad’s left the eight-ball over the pocket ?”
“What’s that Skippy ? Pocket money should be going up in line with inflation ?”

Over the years, this began to seep into my internal monologue… “What’s that Skippy ? I’ve forgotten to close the cursor ?”
It is with thanks to “Uncle Steve” and the help of a know-it-all marsupial with a unique linguistic talent that I will be looking at logging in PL/SQL applications and ways of…well…doing less typing to achieve the same level of instrumentation.
Specifically, what we’ll cover is :

Why logging in PL/SQL is special

Logging error messages by magic

Using OWA_UTIL.WHO_CALLED_ME

Using PL/SCOPE to figure out where you are

An neater way to log runtime parameter values

A logging package that incorporates these techniques

The ultimate combination of all of these changes may well not be ideal in every situation.
However, Skippy has tried to reduce the instrumentation code required to and absolute minimum. After all, kangaroos don’t like typing.

Like most of the Oracle world, Skippy and myself are still on 11gR2.
The sunny uplands of 12c remain, for the moment, the province of messing about in VirtualBox.
Therefore, we won’t be covering any of the 12c utilities ( e.g. UTL_CALL_STACK) here.

What’s that Skippy ? Oh yes, if you are considering a replacement for your existing logging sub-system, or even planning one from scratch, then you might want to check out the OraOpenSource Logger.Continue reading →

When I started writing this post, it was going to be about something else.
This happens occasionally, I have an idea in my head and set to work.
Then I do some research – don’t look so surprised, I do look at the docs occasionally – and, as in this case, I find out that there’s rather more to the topic at hand than I first thought.
What follows is a re-visiting of some of the tools available in Oracle to help with error logging.
It includes stuff that either I’d forgotten or had never considered about some fairly common functions.
Before I dive in, I’d just like to say thanks to William Robertson, who first pointed out to me the similarity between PL/SQL error logging and Quantum Theory. If you’re still unclear of the connection between the two then consider, if you will, the Schrodinger’s Cat Thought Experiment.
It involves locking a cat in a box and possibly poisoning it.
Schrodinger postulates that the cat is both alive and dead…until you open the box to check.
The conclusions we can draw from this experiment are :

According to Quantum Theory, the act of observation changes the nature of the thing being observed

Schrodinger wasn’t a Cat person

Before going any further, I should point out that most of the stuff I know about Physics comes from watching Star Trek.

There are times when I wonder whether DBMS_DATAPUMP isn’t modelled on your average teenager’s bedroom floor.
If you’ve ever tried to start an import by specifying a file that doesn’t exist ( or that DBMS_DATAPUMP can’t see) you’ll know what I mean.
The job fails, which is fair enough. However, DBMS_DATAPUMP then goes into a huff and refuses to “clean up it’s room”.
Deb has suggested that this sort of thing is also applicable to husbands.
Not that I have any idea of whose husband she’s talking about.
Anyway, you may consider it preferable to check that the export file you want to import from actually exists in the appropriate directory before risking the wrath of the temperamental datapump API.
This apparently simple check can get a bit interesting, especially if you’re on a Linux server…Continue reading →