Disabling the Oracle Diagnostic and Tuning Pack APIs – If you want something done, do it yourself

At last, we have reached the final episode of the Star Wars themed odyssey through the tangled web that is Oracle’s Diagnostic and Tuning Pack licensing.
Just as well really, Deb has flatly refused to give over any more evenings to my “research” – i.e. re-watching all of the films. Even the appeal of Ewan MacGregor’s Alec Guiness impression has now waned.
Just to recap then, so far I’ve looked at :

Now, finally we’re going to have a look at how we can minimize the chances of an errant select statement causing a whole heap of trouble.
Yes, we’re going to have a go at disabling access to the Diagnostic and Tuning Pack APIs without (hopefully), breaking anything.
Continue reading

Reverse Engineering a Crud Matrix complete with SQLDeveloper Extension – Version 2

A couple of years ago, I wrote an application to reverse-engineer a CRUD matrix for tables in an Oracle database.
I’ve since used it quite a lot for impact analysis and have refined it a fair amount. I’m now happy enough with the new version to let it take it’s first steps into the wider world….where doubtless people will be able to find some of the bugs that I’ve missed.
At this point, if you’re wondering what a CRUD matrix is, you can have a look at the original post here. Continue reading

Express Yourself – String Comparison in Oracle using REGEXP_REPLACE

As a programmer, one of the absolute pleasures of Unix ( especially when compared to Windows) is the flexibility offerred by the use of Regular Expressions. Not only do tools such as Sed and Awk offer almost unlimited options for manipulating text files, but Regular Expression syntax offers an unparalleled opportunity to exercise the top row of your keyboard.
Regular Expressions have been available in Oracle for a while now, but it’s one of those incredibly useful features that doesn’t seem to be widely used.
This is no doubt due, in part, to the fairly involved syntax, but help is at hand. There’s a particularly good explaination of most of the Oracle Regular Expression functions ( and syntax) on the excellent PSOUG reference site ( formerly known as Morgan’s Library).

I want to concentrate here on the REGEXP_REPLACE function and how you can use it to help when comparing strings in the database that have come from different sources and are, as a result, in different formats. Continue reading