Oracle sqlldr: column not allowed here

Question

Can anyone spot the error in this attempted data load? The '\\N' is because this is an import of an OUTFILE dump from mysql, which puts \N for NULL fields.

The decode is to catch cases where the field might be an empty string, or might have \N.

Using Oracle 10g on Linux.

load data
infile objects.txt
discardfile objects.dsc
truncate
into table objects
fields terminated by x'1F'
optionally enclosed by '"'
(ID INTEGER EXTERNAL NULLIF (ID='\\N'), 
TITLE CHAR(128) NULLIF (TITLE='\\N'),
PRIORITY CHAR(16) "decode(:PRIORITY, BLANKS, NULL, '\\N', NULL)", 
STATUS CHAR(64) "decode(:STATUS, BLANKS, NULL, '\\N', NULL)", 
ORIG_DATE DATE "YYYY-MM-DD HH:MM:SS" NULLIF (ORIG_DATE='\\N'), 
LASTMOD DATE "YYYY-MM-DD HH:MM:SS" NULLIF (LASTMOD='\\N'), 
SUBMITTER CHAR(128) NULLIF (SUBMITTER='\\N'), 
DEVELOPER CHAR(128) NULLIF (DEVELOPER='\\N'), 
ARCHIVE CHAR(4000) NULLIF (ARCHIVE='\\N'), 
SEVERITY CHAR(64) "decode(:SEVERITY, BLANKS, NULL, '\\N', NULL)", 
VALUED CHAR(4000) NULLIF (VALUED='\\N'), 
SRD DATE "YYYY-MM-DD" NULLIF (SRD='\\N'), 
TAG CHAR(64) NULLIF (TAG='\\N')
)

Sample Data (record 1). The ^_ represents the unprintable 0x1F delimiter.

1987^_Component 1987^_\N^_Done^_2002-10-16 01:51:44^_2002-10-16 01:51:44^_import^_badger^_N^_^_N^_0000-00-00^_none

Error:

Record 1: Rejected - Error on table objects, column SEVERITY.
ORA-00984: column not allowed here

Answer 1

BLANKS is an SQL*Loader keyword, not something you can use inside a decode SQL statement - it's treating it as a column name. If it really is an empty (zero-length) string, as may well be the case in a delimited file, in the decode you could use '' instead of BLANKS; but Oracle treats that as null anyway. In which case the decode should be redundant and you can just use a NULLIF as you have for the other columns. If the 'empty' string is actually one or more spaces, you can do something like decode(TRIM(:PRIORITY),'',NULL,'\\N',NULL,:PRIORITY). (You'd need the final default clause for the decode anyway or all values would go to null.)