Database Reference
In-Depth Information
Now selecting from the external table:
EODA@ORA12CR1> select * from csv3;
COL1 COL2
-------------------- --------------------
emergency data load now
more data must load data
How do we know the extraneous
^M
was removed from each line? Check the length of
COL2
:
EODA@ORA12CR1> select length(col2) from csv3;
LENGTH(COL2)
------------
8
14
If the hidden
^M
characters had not been removed, the length of
COL2
would have been at least one byte
longer than the character data within the column (not to mention we could have had some surprises with
searches and joins).
Preprocessing Wrap-Up
The prior examples show the power of external table
PREPROCESSOR
directive. Sure, you could achieve the same
results without preprocessing, but you would have more steps, more code to maintain, and more places where
things can break.
Specific examples were chosen to illustrate the power and flexibility of preprocessing. In the first example, the
file operated on was a shell script; there was no data file. The output of the OS command was the data returned when
selecting from the external table.
In the second example, a base directory was operated on by the preprocessor script. This provided a starting
point for a directory tree search of compressed files, on-the-fly uncompressing of the contents, and then additional
filtering of columns and/or rows with commands like
find
,
cut
, and
zgrep
.
The third example showed a script operating on a CSV file to remove unwanted characters by using
sed
before
displaying the data. This is a common need when transferring files to and from DOS and UNIX/Linux platforms.
These three examples demonstrate distinctive ways to use the
PREPROCESSOR
directive. This provides a base
knowledge of the possible uses and allows you to build on this to simplify your loading requirements.
External Tables Summary
In this section, we explored external tables. They are a feature available with Oracle9
i
and later that may for the most
part replace SQLLDR. We investigated the quickest way to get going with external tables: the technique of using
SQLLDR to convert the control files we have from past experiences. We demonstrated some techniques for detecting
and handling errors via the bad files and, we also explored some multiuser issues regarding external tables. Lastly, we
demonstrated preprocessing techniques to execute operating system commands as a first step when selecting from an
external table.
We are now ready to get into the next section in this chapter, which deals with unloading data from the database.
