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 in a Name ? USER_TAB_COLS and USER_TAB_COLUMNS are different.

My son and I are quite similar in some ways ( although he would vehemently dispute this).
Like me, he works in IT, in his case as a Support Engineer.
Like me, he’s called Mike (well, my Mum likes the name…and I can spell it).
Unlike me – as he would be quick to point out – he still has all his own hair.
These similarities have been known to cause confusion – I’m often contacted by recruitment agents with enticing offers to work on…some newfangled stuff I know nothing about, whilst he’s constantly being offered “exciting” Database related opportunities.

Similar confusion can arise when you’re delving into the Oracle Data Dictionary…

Continue reading

Migrating the XE Database Management Application to a new version of APEX

I must confess to a weakness when it comes to throwing stuff away.
This is particularly true of techie stuff.
Whilst I have occasionally cannibalised an old machine for parts, there is a regrettably large part of the garage reserved for “vintage” hardware that I might just need at some point.

I’ve recently added to this hoard. I’ve finally gone and got a replacement for my ageing netbook.
As part of the configuration of the new machine, I’ve installed Oracle XE again.

I’m now poised to attempt an upgrade to a shiny new version of APEX.

First of all though, if you are similarly keen to upgrade from the venerable APEX 4.0, which XE ships with, to something more modern, your hoarding instincts may kick-in when it comes to the default Database Management Application.

Once you upgrade APEX 4 to any subsequent version, this application “disappears”.
The functionality it offers is readily available through SQLDeveloper (or indeed, any of the major Oracle Database IDE’s).
Alternatively, it’s a fairly simple matter to come up with your own, improved version.

Not convinced ? Oh well, I suppose we’d better save it for re-deployment into your new APEX environment.

What I’m going to cover here is :

  • Backing up the default XE ADMIN application
  • Tweaking the APEX export file
  • Restoring the XE ADMIN application

I’ve tested this process against both APEX4.2 and APEX5.0 running on Oracle XE11g.
In the steps that follow, I’m assuming that you’re upgrading to APEX5.0.
The main difference here is the APEX owning schema.
For APEX4.2, the owner is APEX_040200, in APEX 5.0 it’s APEX_050000.
As the APEX upgrade takes place entirely within the database, the steps that follow are platform independent.

Incidentally, if you’re wondering exactly how you would upgrade XE11g to this APEX version, details will follow in my next post.

NOTE – I’m assuming here that you’re doing this on your own personal playground 11GXE database and have therefore not
worried too much about any security implications for some of the activities detailed below.

Right, let’s get started…
Continue reading

Oracle XE 11g – Getting APEX to start when your database does

They say patience is a virtue. It’s one that I often get to exercise, through no fault of my own.
Usually trains are involved. Well, I say involved, what I mean is…er…late.
I know, I do go on about trains. It’s a peculiarly British trait.
This may be because the highest train fares in Europe somehow don’t quite add up to the finest train service.
We can debate the benefits of British Trains later – let’s face it we’ll have plenty of time whilst we’re waiting for one to turn up. For now, I want to concentrate on avoiding any further drain on my badly tried patience by persuading APEX that it should be available as soon as my Oracle XE database is…
Continue reading

Getting a File Listing from a Directory in PL/SQL

It’s General Election time here in the UK.
Rather than the traditional two-way fight to form a government, this time around we seem to have a reasonably broad range of choice.
In addition to red and blue, we also have purple and – depending on where you live in the country, multiple shades of yellow and green.
The net effect is to leave the political landscape looking not so much like a rainbow as a nasty bruise.

The message coming across from the politicians is that everything that’s wrong in this country is down to foreigners – Eastern Europeans…or English (once again, depending on your location).
Strangely, the people who’ve been running our economy and public services for the last several years tend not to get much of a mention.
Whatever we end up choosing, our ancient electoral system is not set up to cater for so many parties attracting a significant share of support.

The resulting wrangling to cobble together a Coalition Government will be hampered somewhat by our – equally ancient – constitution.

That’s largely because, since Magna Carta, no-one’s bothered to write it down.

In olden times, if you wanted to find out what files were in a directory from inside the database, you’re options were pretty undocumented as well.
Fortunately, times have changed…

What I’m going to cover here is how to use an External Table pre-process to retrieve a file listing from a directory from inside the database.
Whilst this technique will work on any platform, I’m going to focus on Linux in the examples that follow…
Continue reading