Oracle7 Server Utilities

Identifying Datafiles

To specify the datafile fully, use a filename keyword, optionally followed by a file-processing options string. You may specify multiple files by using multiple INFILE keywords. You can also specify the datafile from the command line, using the DATA parameter described

Naming the File

To specify the file containing the data to be loaded, use the INFILE or INDDN keyword, followed by the filename and optional processing options string. A filename specified on the command line overrides the first INFILE or INDDN keyword in the control file. If no filename is specified, the filename defaults to the control filename with an extension or file type of DAT.

If the control file also contains the data to be loaded, specify a filename of "*". This specification works with the BEGINDATA keyword, described .

where:

INFILE or INDDN Either keyword may be used.
filename Name of the file containing the data to be loaded. May be any valid filename for your operating system.
All filenames containing spaces or punctuation marks should be enclosed in single quotation marks. For more details, see " Specifying Filenames and Database Objects" .
* An asterisk (*) replaces a filename to specify that the data is in the control file. If multiple datafiles are specified, this specification must be first.
processing_options This is the file-processing options string. It indicates datafile format. It also optimizes datafile reads. See "Specifying Datafile Format and Buffering" .

Specifying Multiple Datafiles

To load data from multiple datafiles in one run of SQL*Loader, use an INFILE statement for each datafile. Datafiles do not need the same file format, although the layout of the records must be identical. For example, two files could be specified with completely different file processing options strings, and a third could consist of data in the control file.

For each datafile, you can also specify a discard file and a bad file. These files should be declared after each datafile name. The following portion of a control file specifies four files:

INFILE  mydat1.dat  BADFILE  mydat1.bad  DISCARDFILE mydat1.dis

INFILE  mydat2.dat

INFILE  mydat3.dat  DISCARDFILE  mydat3.dis

INFILE  mydat4.dat  DISCARDMAX  10

For the first datafile (MYDAT1.DAT), both a bad file and discard file are explicitly named. So both files are created, if needed.

For the second datafile (MYDAT2.DAT), neither a bad file nor a discard file is specified. So only the bad file is created, if it is needed. If created, the bad file has a default filename and extension. The discard file is not created, even if rows are discarded.

For the third file (MYDAT3.DAT), the default bad file is created, if needed. A discard file with the given name is also created, if it is needed.

For the fourth file (MYDAT4.DAT), the default bad file is created, if needed. Because the DISCARDMAX option is used, SQL*Loader assumes that a discard file is wanted and creates it with the default name (MYDAT4.DSC), if it is needed.

Note: It is not possible to join physical records from separate datafiles into one logical record.

Examples of How to Specify a Datafile

In the first example, you specify that the data is contained in the control file itself:

INFILE  *

In the next example, you specify that the data is contained in a file named WHIRL with the default file extension or file type of DAT:

INFILE  WHIRL

The following example specifies the full path to a file:

INFILE 'c:/topdir/subdir/datafile.dat'

Note: Filenames that include spaces or punctuation marks should be enclosed in single quotation marks. For more details on filename specification, see "Specifying Filenames and Database Objects" .

INFILE or INDDN	Either keyword may be used.
filename	Name of the file containing the data to be loaded. May be any valid filename for your operating system.
All filenames containing spaces or punctuation marks should be enclosed in single quotation marks. For more details, see "	Specifying Filenames and Database Objects" .
*	An asterisk (*) replaces a filename to specify that the data is in the control file. If multiple datafiles are specified, this specification must be first.
processing_options	This is the file-processing options string. It indicates datafile format. It also optimizes datafile reads. See "Specifying Datafile Format and Buffering" .