Oracle sqlldr: column not allowed here
- by Wade Williams
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
