Database Reference
In-Depth Information
now select from the external table to return the ten largest files under the directory (and subdirectories) of
/u01
:
EODA@ORA12CR1> select * from flf;
FNAME BYTES
---------------------------------------- ----------
/u01/dbfile/ORA12CR1/temp01.dbf 1.0737E+10
...
/u01/app/oracle/unloaddir/big_table.dat 2786618287
now suppose you want to change the directory location to search a starting point from
/u01
to
/orahome/oracle
.
Do the following:
EODA@ORA12CR1> create or replace directory data_dir as '/orahome';
EODA@ORA12CR1> alter table flf location(data_dir:'oracle');
now selecting from the external table, the directory path that starts searching for the largest files in a directory
tree is changed:
EODA@ORA12CR1> select * from flf;
FNAME BYTES
------------------------------------------------------------ ----------
/orahome/oracle/orainst/12.1.0.2/database1.zip 1652417511
...
/orahome/oracle/orainst/12.1.0.2/database2.zip 1212882524
So not a typical use of an external table; rather it's an illustration of what's possible with preprocessing.
Trimming Characters Out of a File
I worked with a DBA who would receive an occasional fire-drill manager e-mail with an attached spreadsheet asking,
“Can you load this spreadsheet into the production database?” In this environment, there wasn't direct network
access to the production database; so the DBA didn't have the option of loading over the network via a tool like
SQLLDR. In this situation, the steps for loading data are as follows:
1.
Save the spreadsheet on a Windows laptop as a CSV file.
2.
Copy the CSV file to a secure server configured specifically for file transfers to the
production server, and then copy the CSV file to the production server.
3.
Use an OS utility to remove hidden DOS characters that are embedded in the CSV file.
4.
As the Oracle user, create an external table based on the CSV file.
5.
Use SQL to insert into a production table by selecting from the external table.
Step 3 of the preceding list is what I'll focus on here because this is where the preprocessor eliminates the need
for that step (as for the other steps, the DBA will have to figure something else out long term).
Assume the file to be loaded is called
load.csv
located in the
/tmp
directory and contains the following data:
emergency data|load now^M
more data|must load data^M
