During a direct path load, data conversion occurs on the client side rather than on the server side. This means that NLS parameters in the initialization parameter file (server-side language handle) will not be used. To override this behavior, you can specify a format mask in the SQL*Loader control file that is equivalent to the setting of the NLS parameter in the initialization parameter file, or set the appropriate environment variable. For example, to specify a date format for a field, you can either set the date format in the SQL*Loader control file as shown in Example 12-1 or set an NLS_DATE_FORMAT environment variable as shown in Example 12-2.
Example 12-1 Setting the Date Format in the SQL*Loader Control File
LOAD DATA INFILE 'data.dat' INSERT INTO TABLE emp FIELDS TERMINATED BY "|" ( EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGR NUMBER(4), HIREDATE DATE 'YYYYMMDD', SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) )
Example 12-2 Setting an NLS_DATE_FORMAT Environment Variable
On UNIX Bourne or Korn shell:
% NLS_DATE_FORMAT='YYYYMMDD' % export NLS_DATE_FORMAT
On UNIX csh:
%setenv NLS_DATE_FORMAT='YYYYMMDD'