Reverse Engineering a CRUD Matrix in Oracle (again) – with CRUDO

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, and
this 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).

Continue reading

Becoming Unhinged with INSERT ALL and SEQUENCE.NEXTVAL

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

Caching in – Oracle Sequences and Performance

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

What’s that Skippy ? Mike’s doing too much typing to Instrument his PL/SQL code ?

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

PL/SQL Error Logging and Quantum Theory

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.

Moving on, I now invite you to consider…
Continue reading

Keep your Database Tidy – making sure a file exists before DBMS_DATAPUMP makes a mess

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