Can one skip certain columns while loading data?
One cannot use POSITION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
• CONCATENATE – use when SQL*Loader should combine the same number of physical records together to form one logical record.
• CONTINUEIF – use if a condition indicates that multiple records should be treated as one. Eg. by having a ‘#’ character in column 1.
How can one get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
• A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
• Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can’t use direct load. Refer to chapter 8 on Oracle server Utilities manual.
• Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
• Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.
How does one use SQL*Loader to load images, sound clips and documents?
SQL*Loader can load data from a “primary data file”, SDF (Secondary Data file – for loading nested tables and VARRAYs) or LOBFILE. The LOBFILE method provides an easy way to load documents, photos, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table:
CREATE TABLE image_table (
INTO TABLE image_table
FIELDS TERMINATED BY ','
image_data LOBFILE (file_name) TERMINATED BY EOF
How does one load EBCDIC data?
Specify the character set WE8EBCDIC500 for the EBCDIC data. The following example shows the SQL*Loader controlfile to load a fixed length EBCDIC record into the Oracle Database:
INFILE data.ebc "fix 86 buffers 1024"
INTO TABLE temp_data
field1 POSITION (1:4) INTEGER EXTERNAL,
field2 POSITION (5:6) INTEGER EXTERNAL,
field3 POSITION (7:12) INTEGER EXTERNAL,
field4 POSITION (13:42) CHAR,
field5 POSITION (43:72) CHAR,
field6 POSITION (73:73) INTEGER EXTERNAL,
field7 POSITION (74:74) INTEGER EXTERNAL,
field8 POSITION (75:75) INTEGER EXTERNAL,
field9 POSITION (76:86) INTEGER EXTERNAL