Having spent the last couple of months working with a South African ( hello Trevor) and being reminded, oh so gently, of that nation’s recent cricketing success, I was hoping to get the opportunity to return the favour via the medium of Rugby Union.
The Springboks narrow defeat of England in the Autumn International did little to derail this cunning plan. Unfortunately, my emergency backup nationality failed at a crucial moment as the hitherto invincible All Blacks were roundly thrashed by England a week later.
People in glass-houses…
Anyway, what follows is a solution to a fairly specific problem we were confronted with recently.
Imagine you have a flat file which is loaded by means of SQL*Loader into a database table.
For operational reasons, it is not possible to alter the structure of the table.
The table may look something like this :
CREATE TABLE results( fixture_date DATE NOT NULL, home_team VARCHAR2(30) NOT NULL, away_team VARCHAR2(30) NOT NULL, home_points NUMBER NOT NULL, away_points NUMBER NOT NULL) /
The file that you are required to load into the table is called fixtures.dat and looks like this …
18082012,AUSTRALIA,NEW ZEALAND,19,27 18082012,SOUTH AFRICA,ARGENTINA,27,6 25082012,NEW ZEALAND,AUSTRALIA,22,0 25082012,ARGENTINA,SOUTH AFRICA,16,16 08092012,AUSTRALIA,SOUTH AFRICA,26,19 08092012,NEW ZEALAND,ARGENTINA,21,5 15092012,AUSTRALIA,ARGENTINA,, 15092012,NEW ZEALAND,SOUTH AFRICA,, 29092012,SOUTH AFRICA,AUSTRALIA,, 29092012,ARGENTINA,NEW ZEALAND,, 06102012,SOUTH AFRICA,NEW ZEALAND,, 06102012,ARGENTINA,AUSTRALIA,,
The data is the fixture list for this year’s Rugby Championship. No, I’m not making any comment about any of the results.
Notice a problem ? Yes, the HOME_POINTS and AWAY_POINTS columns in the table are mandatory, but the file itself contains nulls for these values in some of the records.
So the question is – how do we load this file directly into the table using only SQL*Loader ?
The SQL*Loader Control File
These days, you’ll often find SQL*Loader used for External Tables.
In this instance, we’re going to be using this utility in it’s more traditional command-line mode.
Fortunately, the command itself is fairly simple, because most of the messy stuff can be written separately in a control file.
The control file specifies
- the action ( LOAD DATA)
- the file containing the data (INFILE)
- the table to load into
- The delimiter used in the file (FIELDS TERMINATED BY)
- treat any missing values at the end of a line as null (TRAILING NULLCOLS)
Finally, and most significantly from our point of view, it specifies how any data needs to be modified before loading.
The formatting of date data is fairly common place in control files.
However, you can also manipulate individual data items in more sophisticated ways such as, for example, assigning a default value when a null exists…
In this case, the control file looks something like this :
LOAD DATA INFILE 'fixtures.dat' INTO TABLE results APPEND FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( fixture_date DATE "DDMMYYYY", home_team, away_team, home_points "NVL(:home_points,0)", away_points "NVL(:away_points,0)")
Time to put it to the test (NOTE – in this case, I’m connecting to a schema where the RESULTS table already exists) :
$ sqlldr userid=mike control=results.ctl Password: SQL*Loader: Release 220.127.116.11.0 - Production on Thu Dec 27 12:50:46 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 12 $
It looks as if all the rows have been loaded successully so we can now check the actual contents of the table …
SQL> SELECT fixture_date, home_team, away_team, 2 home_points, away_points 3 FROM results 4 WHERE fixture_date = TO_DATE('15092012', 'DDMMYYYY') 5 / FIXTURE_DATE HOME_TEAM AWAY_TEAM HOME_POINTS AWAY_POINTS ------------ -------------------- -------------------- ----------- ----------- 15-SEP-12 AUSTRALIA ARGENTINA 0 0 15-SEP-12 NEW ZEALAND SOUTH AFRICA 0 0 SQL>
This is by no means the neatest way of manipulating data during upload into Oracle and there may well be performance penalties using this method. However, when you’re stuck for a better alternative, it’s worth remembering that SQL*Loader does have a fair amount of flexibility.
There, I’ve managed to get all the way through this post without a single reference to South Africa’s success or otherwise in this particular competition…but the results can be found here if you just have to know how it went for them.
That’s the great thing about writing a blog, you always get the last word…unless a certain Welsh person decides to remind you just which team won the Six Nations this year!