Database Reference
In-Depth Information
Things to note in the .dat file are as follows:
Each field is enclosed in our enclosure character.
The
DATES are unloaded as large numbers.
Each line of data in this file ends with a
~ as requested.
We can now reload this data easily using SQLLDR. You may add options to the SQLLDR command line as
you see fit.
As stated previously, the logic of the unload package may be implemented in a variety of languages and tools.
On the Ask Tom web site, you will find this example implemented not only in PL/SQL as it is here but also in Pro*C
and SQL*Plus scripts. Pro*C is the fastest implementation, and it always writes to the client workstation file system.
PL/SQL is a good all-around implementation (no need to compile and install on client workstations), but it always
writes to the server file system. SQL*Plus is a good middle ground, offering fair performance and the ability to write to
the client file system.
Summary
In this chapter, we covered many of the ins and outs of data loading and unloading. First we discussed the advantages
that external tables have over SQLLDR. Then we looked at easy techniques to get started with external tables. We also
showed examples of using the PREPROCESSOR directive to execute OS commands prior to loading the data.
Then we looked at an Oracle 10 g and above feature, the external table unload, and the ability to easily create and
move extracts of data from database to database. We wrapped this up by investigating how to unload data from a table
into a dump file that can be used to move data from one database to another.
We discussed that in most scenarios you should be using external tables and not SQLLDR. However, there are
some situations that may require the use of SQLLDR, like loading data over the network. We then examined many of
the basic techniques for loading delimited data, fixed-width data, LOBs, and the like.
Finally, we looked at the reverse process, data unloading, and how to get data out of the database in a format
that other tools—such as spreadsheets or the like—may use. In the course of that discussion, we developed a PL/SQL
utility to demonstrate the process—one that unloads data in a SQLLDR (or external table) friendly format, but could
easily be modified to meet your needs.
 
Search WWH ::




Custom Search