Oracle sqlldr：此处不允许列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2436033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sqlldr: column not allowed here
提问by wadesworld
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.
任何人都可以发现此尝试数据加载中的错误吗？这'\\N'是因为这是从 mysql 的 OUTFILE 转储的导入，它为 NULL 字段放置了 \N。
The decode is to catch cases where the field might be an empty string, or might have \N.
解码是为了捕捉字段可能是空字符串或可能有 \N 的情况。
Using Oracle 10g on Linux.
在 Linux 上使用 Oracle 10g。
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.
样本数据（记录 1）。^_ 代表不可打印的 0x1F 分隔符。
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
采纳答案by Alex Poole
BLANKSis an SQL*Loader keyword, not something you can use inside a decodeSQL 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 decodeyou could use ''instead of BLANKS; but Oracle treats that as null anyway. In which case the decodeshould be redundant and you can just use a NULLIFas 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 decodeanyway or all values would go to null.)
BLANKS是一个 SQL*Loader 关键字，而不是您可以在decodeSQL 语句中使用的关键字- 它将其视为列名。如果它确实是一个空（零长度）字符串，就像在分隔文件中的情况一样，decode您可以使用;''代替BLANKS; 但是 Oracle 无论如何都将其视为空值。在这种情况下， thedecode应该是多余的，您可以NULLIF像其他列一样使用 a 。如果“空”字符串实际上是一个或多个空格，则可以执行类似decode(TRIM(:PRIORITY),'',NULL,'\\N',NULL,:PRIORITY). （decode无论如何，您都需要最终的默认子句，否则所有值都将变为 null。）
