0

Consider this SO Question UTF-8 And External Tables. A proposed solution contains VARCHAR2 columns in the CREATE section but those same columns get CHAR attributes in the ACCESS PARAMETERS section.

Why the difference?

enter image description here

2

Datatypes inside the database, and used by SQL*Loader or the ORACLE_LOADER driver are different.

SQL*Loader does not know the VARCHAR2 datatype. It has a VARCHAR type, but that type is nonportable, meaning it is platform dependent.

SQL*Loader Datatypes

VARCHAR fields can be loaded with correct results only between systems where a SHORT data field INT has the same length in bytes. If the byte order is different between the systems, or if the VARCHAR field contains data in the UTF16 character set, then use the appropriate technique to indicate the byte order of the length subfield and of the data. The byte order of the data is only an issue for the UTF16 character set.

The CHAR type of SQL*Loader is portable.

Portable Datatypes

The portable datatypes are grouped into value datatypes and length-value datatypes. The portable value datatypes are as follows:

CHAR

Datetime and Interval

GRAPHIC

GRAPHIC EXTERNAL

Numeric EXTERNAL (INTEGER, FLOAT, DECIMAL, ZONED)

RAW

The portable length-value datatypes are as follows:

VARCHARC

VARRAWC

Using a non-portable type you may or may not be able to load the data on a different platform:

Loading Data Across Different Platforms

When a data file created on one platform is to be loaded on a different platform, the data must be written in a form that the target system can read. For example, if the source system has a native, floating-point representation that uses 16 bytes, and the target system's floating-point numbers are 12 bytes, then the target system cannot directly read data generated on the source system.

The best solution is to load data across an Oracle Net database link, taking advantage of the automatic conversion of datatypes. This is the recommended approach, whenever feasible, and means that SQL*Loader must be run on the source system.

Problems with interplatform loads typically occur with native datatypes. In some situations, it is possible to avoid problems by lengthening a field by padding it with zeros, or to read only part of the field to shorten it (for example, when an 8-byte integer is to be read on a system that uses 4-byte integers, or the reverse). Note, however, that incompatible datatype implementation may prevent this.

If you cannot use an Oracle Net database link and the data file must be accessed by SQL*Loader running on the target system, then it is advisable to use only the portable SQL*Loader datatypes (for example, CHAR, DATE, VARCHARC, and numeric EXTERNAL).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.