The following restrictions apply when you use the EXTERNAL_TABLE qualifier:
■ Julian dates cannot be used when you insert data into a database table from an external table through SQL*Loader. To work around this, use TO_DATE and TO_
CHAR to convert the Julian date format, as shown in the following example:
Note: The EXTERNAL_TABLE=EXECUTE qualifier tells
SQL*Loader to create an external table that can be used to load data and then execute the INSERT statement to load the data. All files in the external table must be identified as being in a directory object.
SQL*Loader attempts to use directory objects that already exist and that you have privileges to access. However, if SQL*Loader does not find the matching directory object, it attempts to create a temporary directory object. If you do not have privileges to create new directory objects, then the operation fails.
To work around this, use EXTERNAL_TABLE=GENERATE_ONLY to create the SQL statements that SQL*Loader would try to execute.
Extract those SQL statements and change references to directory objects to be the directory object that you have privileges to access.
Then, execute those SQL statements.
See Also:
■ SQL*Loader Case Studies on page 6-12 for information on how to access case studies
■ Chapter 12, "External Tables Concepts"
■ Chapter 13, "The ORACLE_LOADER Access Driver"
Command-Line Parameters
TO_CHAR(TO_DATE(:COL1, 'MM-DD-YYYY'), 'J')
■ Built-in functions and SQL strings cannot be used for object elements when you insert data into a database table from an external table.
FILE (file to load into)
Default: none
FILE specifies the database file to allocate extents from. It is used only for parallel loads. By varying the value of the FILE parameter for different SQL*Loader processes, data can be loaded onto a system with minimal disk contention.
LOAD (records to load)
Default: All records are loaded.
LOAD specifies the maximum number of logical records to load (after skipping the specified number of records). No error occurs if fewer than the maximum number of records are found.
LOG (log file)
Default: The name of the control file, with an extension of .log.
LOG specifies the log file that SQL*Loader will create to store logging information about the loading process.
MULTITHREADING
Default: true on multiple-CPU systems, false on single-CPU systems This parameter is available only for direct path loads.
By default, the multithreading option is always enabled (set to true) on multiple-CPU systems. In this case, the definition of a multiple-CPU system is a single system that has more than one CPU.
On single-CPU systems, multithreading is set to false by default. To use
multithreading between two single-CPU systems, you must enable multithreading; it will not be on by default. This will allow stream building on the client system to be done in parallel with stream loading on the server system.
Multithreading functionality is operating system-dependent. Not all operating systems support multithreading.
PARALLEL (parallel load)
Default: false
PARALLEL specifies whether direct loads can operate in multiple concurrent sessions to load data into the same table.
See Also: Parallel Data Loading Models on page 11-22
See Also: Optimizing Direct Path Loads on Multiple-CPU Systems on page 11-17
See Also: Parallel Data Loading Models on page 11-22
Command-Line Parameters
PARFILE (parameter file)
Default: none
PARFILE specifies the name of a file that contains commonly used command-line parameters. For example, the command line could read:
sqlldr PARFILE=example.par
The parameter file could have the following contents:
USERID=scott/tiger CONTROL=example.ctl ERRORS=9999
LOG=example.log
READSIZE (read buffer size)
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader on page 7-1.
The READSIZE parameter is used only when reading data from datafiles. When reading records from a control file, a value of 64 kilobytes (KB) is always used as the READSIZE.
The READSIZE parameter lets you specify (in bytes) the size of the read buffer, if you choose not to use the default. The maximum size allowed is 20 megabytes (MB) for both direct path loads and conventional path loads.
In the conventional path method, the bind array is limited by the size of the read buffer. Therefore, the advantage of a larger read buffer is that more data can be read before a commit operation is required.
For example:
sqlldr scott/tiger CONTROL=ulcas1.ctl READSIZE=1000000
This example enables SQL*Loader to perform reads from the external datafile in chunks of 1,000,000 bytes before a commit is required.
The READSIZE parameter has no effect on LOBs. The size of the LOB read buffer is fixed at 64 kilobytes (KB).
See BINDSIZE (maximum size) on page 7-3.
RESUMABLE
Default: false
The RESUMABLE parameter is used to enable and disable resumable space allocation.
Because this parameter is disabled by default, you must set RESUMABLE=true in order to use its associated parameters, RESUMABLE_NAME and RESUMABLE_TIMEOUT.
Note: Although it is not usually important, on some systems it may be necessary to have no spaces around the equal sign (=) in the parameter specifications.
Note: If the READSIZE value specified is smaller than the BINDSIZE value, the READSIZE value will be increased.
Command-Line Parameters