Oracle Float to Integer fun and Sachin Tendulkar

I recently spent some time working with Venkata, an Oracle programmer and keen cricket fan :

Venkata smiling the smile of a man whose team have won the cricket world cup twice more than...er...Wales.

Venkata smiling the smile of a man whose team have won the cricket world cup twice more than…er…Wales.

This post is about the fun and games involved in the bulk loading of data into Oracle – especially when it includes floating point values.
In Venkata’s honour, I’m going to explore this topic through the medium of the career of Sachin Tendulkar…

At this point it’s probably worth wandering off the point to explore some of the highlights of The Little Master’s career.

India’s tour of England in 1996 has been largely forgotten. However, it proved to be a watershed in our hero’s test career.
The first test of the series at Edgebaston was not untypical of the time – Indian batsmen skittled in conditions totally alien to them. The could only muster a paltry 219 in their second innings and none of them got beyond 18…apart from Tendulkar, ninth out for 122, made with a serenity that contrasted starkly with the chaos surrounding him.
It was in the very next match, at Lords, that India granted test debuts to two batsmen who themselves would prove quite useful over the years. Surav Ganguly marked the occasion with a century. Rahul Dravid fell an agonising five runs short of doing the same.
The fourth member of India’s vaunted middle-order announced himself in the next series we look at, against Australia.
Having been soundly beaten in the first test of the series, India came back the hard way in the second test.
VVS Laxmans 281 not out, with the not inconsiderable assistance of Dravid (180) and Harbhajan (13-196) became only the third team in the history of Test Cricket to win after following on. This against arguably the best team to have ever played the game who had their record winning streak of 16 consecutive tests ended somewhat emphatically.
The 2004/05 series against Bangladesh is included because Tendulkar’s highest test score (248 not out) was made in this series.
The 2009/10 series against South Africa is indicative of the journey that India made during the course of Tendulkar’s career.
At this time they were vying with South Africa for the status of the world’s leading test nation.

Back to the techie stuff. What I’m going to cover is :

  • What happens when you insert a floating point value into an INTEGER column
  • How you can overcome this using SQL*Loader
  • An alternative approach using external tables

Continue reading