Database Reference
In-Depth Information
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1)
)
organization external
( type oracle_datapump
default directory TMP
location( 'allobjects.dat' )
);
You would be set to read that unloaded data using SQL immediately. That is the power of the
Data Pump
file
format: immediate transfer of data from system to system, over “sneakernet” if need be. Think about that the next time
you'd like to take a subset of data home to work with over the weekend while testing.
Even if the database character sets differ (they did not in this example), Oracle has the ability now to recognize
the differing character sets due to the
Data Pump
format and deal with them. Character-set conversion can be
performed on the fly as needed to make the data “correct” in each database's representation.
Again, we'll come back to the
Data Pump
file format in Chapter 15, but this section should give you an overall feel
for what it is about and what might be contained in the file.
Flat Files
Flat files have been around since the dawn of electronic data processing. We see them literally every day. The text alert
log described previously is a flat file.
I found the following definition for “flat file” on the Web, and feel it pretty much wraps things up:
“An electronic record that is stripped of all specific application (program) formats. This allows the
data elements to be migrated into other applications for manipulation. This mode of stripping
electronic data prevents data loss due to hardware and proprietary software obsolescence.”
1
A flat file is simply a file whereby each “line” is a “record,” and each line has some text delimited, typically by a
comma or pipe (vertical bar). Flat files are easily read by Oracle using either the legacy data-loading tool
SQLLDR
or
external tables. In fact, I will cover this in detail in Chapter 15 (External tables are also covered in Chapter 10).
However, flat files are not something produced so easily by Oracle. For whatever reason, there is no simple
command-line tool to export information to a flat file. Tools such as APEX, SQL Developer, and Enterprise Manager
facilitate this process, but there are no official command-line tools that are easily usable in scripts and such to
perform this operation.
That's one reason I decided to mention flat files in this chapter: to propose a set of tools capable of producing
simple flat files. I have over the years developed three methods to accomplish this task, each appropriate in its own
right. The first utility uses a combination of PL/SQL and UTL_FILE with dynamic SQL to accomplish the job. With
small volumes of data (hundreds or thousands of rows), this utility is sufficiently flexible and fast enough to get the job
done. However, it must create its files on the database server machine, which is sometimes not the location we'd like
for them. To that end, I have a SQL*Plus utility that creates flat files on the machine that is running SQL*Plus. Since
SQL*Plus can connect to an Oracle server anywhere on the network, this gives us the ability to unload to a flat file any
