Oracle sqlldr: column not allowed here

Posted by Wade Williams on Stack Overflow See other posts from Stack Overflow or by Wade Williams
Published on 2010-03-12T21:29:58Z Indexed on 2010/03/12 21:37 UTC
Read the original article Hit count: 452

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about oracle10g

Related posts about sqlloader