Data Type Conversions

The data type specifications in the control file tell SQL*Loader how to interpret the information in the data file. The server defines the data types for the columns in the database. The link between these two is the column name specified in the control file.

SQL*Loader extracts data from a field in the input file, guided by the data type specification in the control file. SQL*Loader then sends the field to the server to be stored in the appropriate column (as part of an array of row inserts).

SQL*Loader or the server does any necessary data conversion to store the data in the proper internal format. This includes converting data from the data file character set to the database character set when they differ.


When you use SQL*Loader conventional path to load character data from the data file into a LONG RAW column, the character data is interpreted has a HEX string. SQL converts the HEX string into its binary representation. Be aware that any string longer than 4000 bytes exceeds the byte limit for the SQL HEXTORAW conversion operator. An error will be returned. SQL*Loader will reject that row and continue loading.

The data type of the data in the file does not need to be the same as the data type of the column in the Oracle table. The Oracle database automatically performs conversions, but you need to ensure that the conversion makes sense and does not generate errors. For instance, when a data file field with data type CHAR is loaded into a database column with data type NUMBER, you must ensure that the contents of the character field represent a valid number.


SQL*Loader does not contain data type specifications for Oracle internal data types such as NUMBER or VARCHAR2. The SQL*Loader data types describe data that can be produced with text editors (character data types) and with standard programming languages (native data types). However, although SQL*Loader does not recognize data types like NUMBER and VARCHAR2, any data that the Oracle database can convert can be loaded into these or other database columns.