Oracle DUL newlines

mode

Stream mode is the most compact SQL*Loader format, the records to be loaded are variable in size and terminated

by a newline. This mode is only suitable for data without embedded newlines, as any newlines will look like the

termination of the current record. This is the default mode, and can be explicitly set by setting LDR_PHYS_REC_SIZE= 0.

Fixed Length Physical Records

The almost universal SQL*Loader output mode. Each logical record will be made up of several physical records.

This requires more space but allows to have embedded newlines in the user data. A suggested value is

LDR_PHYS_REC_SIZE = 81, this allows one physical record per line on a classic terminal.

Export Mode

In this mode DUL generates a minimal format (very old style header, just the insert statement and its data) that imp

can process. Originally invented to preserve the contents of long raws. The disadvantage is that no column

conversion is done, so corrupted columns go undetected. To configure this set EXPORT_MODE =TRUE in init.dul.

Export_mode is not supported when using unexp or unpump.

Long raws and blobs

For long raws and blobs DUL will be converted to hex strings, (that will be twice as long, as each byte requires two

hex nibbles). This works beautifully for if the data is loaded into a long raw column, the database automatically does

the correct conversion. However for BLOBs this conversion is not done, and the hex strings are loaded directly.

The work around is to convert this later using SQL, store it in an intermediate long raw, or to use export mode, or use

USE_LOB_FILES = TRUE.

Lob Files

A variation in output mode is USE_LOB_FILES = TRUE. In this mode every CLOB or BLOB is stored directly

untransformed into a lob file. This method preserves all contents correctly. It is in general not suitable for tables with

many lobs as this creates many operating system files, and operating systems and their utilities generally do not

work very efficient with a million files in a single directory.

Character Sets

DUL typically does not do any character set conversion. However sometimes it has to. All output needs to be in the

same character set, but NVARCHAR or CLOBS can use a different character set. The only conversion that DUL can

do is to convert some character sets to UTF8 when using SQL*Loader mode. Set LDR_OUTPUT_IN_UTF8 = TRUE to

configure conversion to UTF8.