Oracle SQL*Loader: The Definitive Guide

Chapter 8. Transforming Data During a Load

It sure would be nice if all the data to be loaded was in exactly the right format to be dumped directly into our databases. Unfortunately, in the real world, that’s often not the case. For example, you may need latitude and longitude expressed as decimal numbers, but instead you might get separate degree, minute, and second values. If you control the source of the data, you probably can format and organize it in a way that makes it easy to load using SQL*Loader. But if you’re working with data from an external source, you may have to take it the way you get it. That’s when it’s nice to have some options for manipulating data as you load it.

One of the most convenient mechanisms for manipulating data during a load is to take advantage of the ability to define SQL expressions for the fields that you are loading. These SQL expressions operate on the data in one or more fields in order to return a result that is then loaded into the destination database column. When writing SQL expressions, you can draw from Oracle’s rich palette of built-in functions, or you can write your own functions. Another approach to modifying data is to load it into a temporary work table from which you can further read and process the data. A creative variation on the work table approach is to use triggers to automatically kick off processing of the new rows being loaded.

Using Oracle’s Built-in SQL Functions

One of the most powerful capabilities at your disposal ...