However, in a data warehouse
environment where an external table is read in its entirety for a merge operation with an existing
table, these drawbacks do not apply.
You might use an external table to gather employee suggestions in a web-based front end that
does not have access to the production database; in this example, you will create an external
table that references a text-based file containing two fields: the employee ID and the comment.
First, you must create a directory object to point to the operating system directory where the
text file is stored. In this example, you will create the directory EMPL_COMMENT_DIR to reference
a directory on the Unix file system, as follows:
SQL> create directory empl_comment_dir as
2 '/u10/Employee_Comments';
Directory created.
The text file in this directory is called empl_sugg.txt, and it looks like this:
$ cat empl_sugg.txt
101, The cafeteria serves lousy food.
138, We need a raise.
556 Oracle Database 11g DBA Handbook
112, There are not enough bathrooms in Building 5.
138, I like the new benefits plan.
$
Because this text file has two fields, you will create the external table with two columns, the
first being the employee number and the second being the text of the comments. Here is the
create table command:
SQL> create table empl_sugg
2 (employee_id number,
3 empl_comment varchar2(250))
4 organization external
5 (type oracle_loader
6 default directory empl_comment_dir
7 access parameters
8 (records delimited by newline
9 fields terminated by ','
10 (employee_id char,
11 empl_comment char)
12 )
13 location('empl_sugg.
Pages:
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832