10, 11 THE COLUMNS INTO WHICH TO INSERT THE DATA.THIS IS A VERY SIM...

9, 10, 11 The columns into which to insert the data.

This is a very simple example. The syntax of the controlfile can handle a wide

range of formats with intelligent parsing to fix any deviations in format such as length

or data types. In general you can assume that it is possible to construct a controlfile

that will understand just about any input datafile. However, do not think that it is

always easy.

TIP It may be very difficult to get a controlfile right, but once you have it, you

can use it repeatedly, with different input datafiles for each run. It is then the

responsibility of the feeder system to produce input datafiles that match your

controlfile, rather than the other way around.

External Tables

An external table is visible to SELECT statements as any other table, but you cannot

perform DML against it. This is because it does not exist as a segment in the database:

it exists only as a data dictionary construct, pointing toward one or more operating

system files. Using external files is an alternative to using SQL*Loader, and is often

much more convenient.

The operating system files of external tables are located through Oracle directory

objects. Directories are also a requirement for Data Pump, discussed later in this

chapter.

Directories

Oracle directories provide a layer of abstraction between the user and the operating

system: you as DBA create a directory object within the database, which points to a

physical path on the file system. Permissions on these Oracle directories can then be

granted to individual database users. At the operating system level, the Oracle user

will need permissions against the operating system directories to which the Oracle

directories refer.

Directories can be created either from a SQL*Plus prompt or from within Database

Control. To see information about directories, query the view DBA_DIRECTORIES.

Each directory has a name, an owner, and the physical path to which it refers. Note that

Oracle does not verify whether the path exists when you create the directory—if it does

not, or if the operating system user who owns the Oracle software does not have

P AR T III

permission to read and write to it, you will only get an error when you actually use the

directory. Having created a directory, you must give the Oracle database user(s) who

will be making use of the directory permission to read from and write to it, just as your

system administrators must give the operating system user permission to read from

and write to the physical path.

EXAM TIP Directories are always owned by user SYS, but any user to whom

you have granted the CREATE ANY DIRECTORY privilege can create them.

Figure 23-1 demonstrates how to create directories, using SQL*Plus. In the figure,

user SCOTT attempts to create a directory pointing to his operating system home

directory on the database server machine. This fails because, by default, users do not

have permission to do this. After being granted permission, he tries again. As the

directory creator, he will have full privileges on the directory. He then grants read

permission on the directory (and therefore any files within it) to all users, and read

and write permission to one user. The query against ALL_DIRECTORIES shows that

the directory (like all directories) is owned by SYS: directories are not schema objects.

This is why SCOTT cannot drop the directory, even though he created it.

Using External Tables

A common use of external tables is to avoid the necessity to use SQL*Loader to read

data into the database. This can give huge savings in the ETL (extract-transform-load)

cycle typically used to update a DSS system with data from a feeder system. Consider

the case where a feeder system regularly generates a dataset as a flat ASCII file, which

should be merged into existing database tables. One approach would be to use

SQL*Loader to load the data into a staging table, and then a separate routine to

merge the rows from the staging table into the DSS tables. This second routine cannot

start until the load is finished. Using external tables, the merge routine can read the

source data from the operating system file(s) without having to wait for it to be loaded.