Databases Reference
In-Depth Information
business area in a text format, the database's copy of the data most likely will be out
of synch until the next time you import it. If you treat the external data as a table, it
will always be up to date every time you access it as an external table.
There are a few drawbacks to using external tables. External tables are read-
only; changes cannot be made to the external data source with UPDATE state-
ments. Also, external tables cannot be indexed. Therefore, if you need to access
only a small fraction of the rows in the external table, an internal table with an
index might be a better solution.
Janice, the DBA, has been assigned the task of making the customer feed-
back files maintained by the Customer Service group accessible from within the
database. Currently, the Customer Service group receives customer feedback,
which is entered on a daily basis into a text file on the shared network drive
I:\Common\CustomerComments with a filename of feedback.txt.
The first step Janice must perform is to define an Oracle object known as
a
directory
. An Oracle directory is an Oracle object that contains an alias to a
directory path on the operating system's filesystem. Once defined in this manner,
the Oracle directory object can be used to refer to the location on the filesystem
in subsequent Oracle commands, such as the CREATE TABLE ... ORGANIZATION
EXTERNAL command. You need to run the CREATE DIRECTORY command only
once for each filesystem pathname you want to access. Janice's command for
creating this directory object is as follows:
directory
A database object that stores a reference
to a directory on the host operating sys-
tem's filesystem.
create directory comment_dir as
'I:\Common\CustomerComments';
Directory created.
The file that contains the data for the external table, feedback.txt, looks
like this:
154,Helpful and Friendly.
150,Took the time to help me buy the widgets I really needed.
156,Didn't really seem too enthusiastic.
152,The Best experience I've had with Widgets-R-Us.
The external table will have two columns: The first field is the employee
number, and the second field is the text of the comments from the customer.
A comma separates the employee number from the comment. Janice uses the fol-
lowing CREATE TABLE statement to create the external table:
create table cust_comments (
employee_id number,
comments varchar2(100))
organization external
(default directory comment_dir
