Gits and Giggles – Getting onto Github from Mint

“Github ?” said Deb, “sounds like a chat room for grumpy old men. You should fit right in !”
To be fair, neither of us were in a particularly good mood at the time.
Deb had just been made to sit through the Rugby World Cup Final whilst my emergency backup nationality had finally born fruit.
All I said to her was that it’s nice to be able to support a real country rather than a mere principality, like Wales. Honestly, some people are so touchy.

For my part, I had just discovered that Github, based on the Git source control system written by Linus Torvalds himself, has integrated clients for Windows and Mac, but not for Linux.

No matter. If you want to interact with Github, you’ll need to have Git installed on your client machine anyway and, mine being Linux, there are a number of GUIs available for Git.

Aside from the Git documentation itself, which is extensive, there are a number of excellent guides to both Git and Github available.
Rather than re-hashing these – although I will link some of them – I’m going to look at things from a slightly different perspective.

Throughout my career, I’ve been, first and foremost, a database developer.
Way back when, choices of version control systems were rather limited. In a professional sense, I grew up with PVCS and Visual Source Safe.
Even later on, the fact that Oracle Forms and Reports were binary source code meant that the Edit-Merge paradigm of source control was something that tended not to gain traction in the Oracle Shops that I worked in.

Later on, in larger organisations, Perforce was the tool of choice, although always with the comforting P4Win ( and later P4V) front-end.

So, I’d rather like the comfort of a GUI, if only to see that the files I think I’ve checked in are actually there.

Additionally, Github uses an enhanced version of the Markdown language for text files. It would be nice to be able to preview these files before uploading them to the repository.

First things first then…. Continue reading

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

User-Defined Context Menus in SQLDeveloper

“What are those birds ?”, Deb asked as we lay drowsing by the pool. “Must be seagulls”, I replied confidently.
We may have been in Tenerife rather than Southend, but they were definitely gulls, and we were right next to the sea.
“They’re not seagulls”, Deb stated emphatically, “if they were, they’d be squawking like a bunch of Welsh women fighting over the last pair of shoes in the sale!”
I forbore to comment on this. After all, I’d had recent, painful, experience of Welsh squawking the previous evening.
We’d arrived at the hotel, just in time to watch England come second in the Rugby…to Wales.
Let me tell you squawking wasn’t the half of it.
As usual my emergency backup nationality ( born in Auckland) was no defence against the joyous derision pouring forth from my better half.

Fortunately, the Spanish aren’t big on Rugby so I’ve had a week of relative quiet on that front.
As you’ve no doubt noticed by now, I’m not much of an ornithologist, so I’ve had to find something else to help while away the long hours beside the pool.

Step forward something that I’ve been puzzling over for some time, namely, just what options are available if you want to put together a User-Defined Context Menu for SQLDeveloper.

About now, someone is bound to mention Java. However, my Java is more rusty than a 1973 Ford Cortina that’s been parked in Cardiff Bay for two weeks.

Besides, it’s perfectly possible to knock up some fairly respectable SQLDeveloper extensions using just XML and some SQL and PL/SQL. However, when it comes to Context Menus there doesn’t seem to be one single place for definitive documentation.
Therefore, after much trial and error, I’m going to take this opportunity to set out the options that I have managed to get working, together with examples.

What I’m going to cover is :

  • How to add a context menu to SQLDeveloper
  • Where on the Navigator Tree you can add it
  • How to get more than one item on a Context Menu
  • The various types of input field you can code
  • How to use Context Menus to execute SQL statements and PL/SQL blocks

Before we go any further, I think I should state that these examples were written and tested on SQLDeveloper 4.0.3 running against an Oracle XE 11g database.
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