Oracle – for when it was like that when you got there

Main menu

Post navigation

Nested Tables – Flat-packed data in an Oracle Table

In the aftermath of the holiday season, there follows the inevitable January sales.
This year, I have been spared the inevitable trudge around the stores. Deb has hurt her knee and has therefore been restricted to browsing on-line.

I thought she “kneeded” cheering up, but to date, my attempts at lightening the mood, seem only to have given her the “kneedle”.

Sitting quietly, whilst Deb is wandering through various furniture store websites, I had cause to reflect on Oracle’s own version of Nested Tables.
These were introduced way back in Oracle 8, when Oracle confidently predicted that the Object-Relational Database was the way of the future.
Imagine if they were just bringing this feature out now. You can picture it. Larry would have spent months making disparaging remarks about IKEA’s occasional table range, before unveiling his own version, which was better, cheaper and more efficient.

Whilst you’re never going to be able to rest your pint on one, a Nested Table in Oracle may be useful on occasion.

The Application Error Log

In many Oracle database applications you will find a utility that’s used for debugging issues.
It consists of :

At this point, it’s worth considering the parameters column. Yes, it is a VARCHAR2(4000) and whilst this may, in exceptional circumstances, prove to be too small to hold the requisite data, on the whole it should be OK.
Unfortunately, getting the required data ( in this case the parameter names and values) into the required format to go into the table requires a fair bit of messing about.

Of course, you can have a peek in the data dictionary to get the parameter names by means of DBA_ARGUMENTS. You could then use this information in the error logging procedure meaning that you only had to build a string of parameter values in the order in which they appear in the program. However, looking at the parameter column, especially when there are a large number of parameters passed into the program in question, can get a bit tedious.

Let’s take a different approach which, for the want of a better gag, we’ll call the IKEA method.

Object Relational thingys

The steps for this are :

create a database type to hold the data

create another type – essentially an arary of the first type

create the table with a column defined as the array type

enter and retrieve the data using the new column

The base type essentially defines a record. The record consists of two fields – the parameter name and the parameter value :