SQL*Loader (sqlldr) and NUL delimiter

From: Ed Avis <ed_at_membled.com>
Date: Thu, 08 Apr 2004 16:43:36 +0100
Message-ID: <l14qruqxuf.fsf_at_budvar.future-i.net>

The following format file works with sqlldr from the Oracle 8.1.7 client but it gives an error with the sqlldr from 9.2.0.3.0. I would like to use a zero byte (ASCII NUL) as the delimiter between fields.

LOAD DATA
INFILE 'country_rating_type.fil' "str '\f'" REPLACE
INTO TABLE country_rating_type
FIELDS TERMINATED BY X'0'
TRAILING NULLCOLS
(

    country_rating_type_id INTEGER EXTERNAL,     name CHAR,
    description CHAR,
    deprecated_ind CHAR
)

The "TERMINATED BY X'0'" is the problematic part. It gives the error

SQL*Loader-350: Syntax error at line 5.
Illegal hexadecimal literal specified for delimiter - not valid characters
FIELDS TERMINATED BY X'0' If I change to some other terminator it works; in particular X'5' works (or at least the load gets further). So it is not the X syntax but rather a particular objection to this character, zero. The SQL*Loader control file reference shows X in the syntax diagram.

I have tried putting 'CHARACTERSET US7ASCII' as the second line of the control file, after 'LOAD DATA', but I still get the same error. Perhaps Oracle's idea of US7ASCII does not include ASCII NUL.

I thought it might be related to NLS_LANG, since according to the SQL*Loader docs NLS_LANG determines the character set for the control file. But the control file works with the 8.1.7 sqlldr connecting to the same server, and AFAIK the NLS_LANG for the client is set when it connects to the server. Just to make sure I said in sqlplus

    select userenv('language') from dual;

This gives AMERICAN_AMERICA.US7ASCII with both the 8.1.7 client software (where sqlldr works) and 9.2.0.3.0 (where it does not).

What should I do to load NUL-separated fields with sqlldr?

-- 
Ed Avis <ed_at_membled.com>

Received on Thu Apr 08 2004 - 17:43:36 CEST