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

Main menu

Post navigation

Oracle Schema Differences – keeping up with the Prefix Pixie

Saturday 11th September 1976. That was the day that my Dad first took me to see the (occasionally) mighty West Ham United.
The opponents, the rather more often mighty Arsenal.
I still have vivid memories of that game. The noise from the crowd. The fact that the grass looked so green, brighter than on the TV.
West Ham not playing very well. Frank Stapleton putting a bit of a downer on the day by having the temerity to score twice in a 2-0 win for the Gunners.
My Dad recently celebrated his 70th birthday.
His present from his first-born son ? A trip to see the (previously) mighty Luton Town take on the ( probably must have been from time to time) mighty Nuneaton Borough.
Now, this may seem poor reward for my dear old Dad – he takes me to see two of the top teams in the country and he gets the Blue Square Premier League in return.
Additionally, these days it’s less the colour of the grass that assaults the senses than the colour of the boots.
These are various flourescent colours, virtually none of them black.
Mind you, as Deb pointed out, if you play for Luton and spend most of your working life dressed in bright orange, then accessorising must be a bit of a challenge.
The game itself however, is another matter.
Typical English Football – very quick, lots of commitment. You can tell it’s not the Premiership by the absence of millionaires rolling around the floor in apparent agony because they’ve broken a finger-nail.
Two late goals sends the Hatters home happy.
All of which has nothing to do with the subject of this post, apart from my choice of examples.
Comparing the table structure between different schemas is standard functionality for any self-respecting IDE. However, things get a bit more tricky if you’ve had a visit from the Prefix Pixie. He, she (or it if you’re table relationship diagram dropped out of a design tool) thought it’d be a good idea to give the same prefix to every table in the schema.

The result of this is that the tools in the IDE can’t recognize that tables with different names are meant to have identical structures.
So much for the “Premiership” of Database Development, it looks like we’ll just have to do a bit of D.I.Y. to see through the poxie pixie dust.
Dad would approve.

The tables

In this example, we have a table to hold details of Luton’s current meteoric…er…not-quite-rise-to-the-top of the league.
We also have a separate schema to hold archived records from the current table. The current table looks like this :

Keeping these tables in synch is going to be an ongoing maintenance overhead.
Say, for example, we want to make some changes to the master table :

ALTER TABLE cmonu_hatters MODIFY ( season NOT NULL);
-- Simon's got a good feeling about this season, so increase the size of gl_for
ALTER TABLE cmonu_hatters MODIFY( gl_for NUMBER(3));

Immediately we can see there might be problems. the OLD_HATTERS table only has gl_for defined as a NUMBER(2).
If Simon is right, then when we come to insert this season’s results into the table there will be problems.

There’s a Dictionary View for that

As I’ve observed before, there is a wealth of information in the Data Dictionary that people tend to forget about.
In this case, it’s DBA_TAB_COLS that can come to our rescue :

We can also use this view to identify any columns that exist in the master table but not in the target :

ALTER TABLE cmonu_hatters ADD( position NUMBER(2));
UPDATE cmonu_hatters
SET position = 2
WHERE season = 2009
/
UPDATE cmonu_hatters
SET position = 3
WHERE season = 2010
/
UPDATE cmonu_hatters
SET position = 5
WHERE season = 2011
/
COMMIT;

Generating DDL…carefully

At this point, it would be a simple matter to generate the required DDL to synchronize the archive table with the master table. However, as always, we need to be a bit careful here. Remember, there is more than one way to inject SQL into a database.

The statements we want to generate follow the format :

ALTER TABLE schema.table name MODIFY column namedatatype

… for column changes and :

ALTER TABLE schema.table name ADD column namedatatype

… for new columns.

To build this safely, we need to be sure that the schema, table_name and column_name do not contain any nasty surprises of the SQL Injection variety.

At this point, knowing our application, we can reasonably assume that all of these objects follow these naming conventions :

they contain only alphanumeric characters and the underscore character

they do not contain spaces

We also know that, both the schemas and table_names are already valid object names in the database as we’re retrieving them from the data dictionary. Therfore utilities such as DBMS_ASSERT.SCHEMA_NAME are not going to tell us anything we don’t already know.
What we really need to do is to check each of the items we’re going to concatenate into our DDL to make sure that there’s nothing untoward happening. To do this, we’ll just knock together a simple function like this :

CREATE OR REPLACE FUNCTION is_clean_fn( i_object_name IN VARCHAR2)
RETURN BOOLEAN IS
-------------------------------------------------------------------------------
-- Function to determine whether input that may be used to dynamically
-- build statements contains "allowable" characters or whether it may
-- possibly be an injectable statement.
-- The assumption is that an object name ( in this case, including user supplied
-- parameter values), will contain only alphanumeric and '_' characters.
--
-------------------------------------------------------------------------------
BEGIN
--
-- Make sure that this object name does not contain any spaces or
-- punctuation, apart from '_'
--
IF REGEXP_INSTR( REPLACE( i_object_name, '_'), '[[:punct:]]|[[:space:]]') > 0
THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END is_clean_fn;
/

I’ve taken the precaution of outputting these statements rather than executing them in the code so that I can double-check them rather than risk something unfortunate happening to my tables.

Funny thing nostalgia…I remember back when England batsmen couldn’t play spin and their spinners were absolutely awful at bowling out Indian batsmen. Fortunately, I would like to think that those days were called the 20th century (although I fear they could also be called…next week).