Oracle sqlldr：此处不允许列
任何人都可以发现此尝试的数据加载中的错误吗？ '\\N' 是因为这是从 mysql 导入 OUTFILE 转储，其中 \N 表示 NULL 字段。
解码是为了捕获字段可能为空字符串或可能有 \N 的情况。
在 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')
)
样本数据（记录 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
错误：
Record 1: Rejected - Error on table objects, column SEVERITY.
ORA-00984: column not allowed here
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
BLANKS是一个 SQL*Loader 关键字，不能在decodeSQL 语句中使用 - 它将其视为列名。如果它确实是一个空（零长度）字符串，就像分隔文件中的情况一样，则在decode中，您可以使用''而不是空白；但 Oracle 无论如何都会将其视为空。在这种情况下，decode应该是多余的，您可以像其他列一样使用NULLIF。如果“空”字符串实际上是一个或多个空格，您可以执行类似decode(TRIM(:PRIORITY),'',NULL,'\\N',NULL,:PRIORITY)的操作。 （无论如何，您都需要decode的最终默认子句，否则所有值都将变为 null。）BLANKSis an SQL*Loader keyword, not something you can use inside adecodeSQL 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 thedecodeyou could use''instead ofBLANKS; but Oracle treats that as null anyway. In which case thedecodeshould be redundant and you can just use aNULLIFas you have for the other columns. If the 'empty' string is actually one or more spaces, you can do something likedecode(TRIM(:PRIORITY),'',NULL,'\\N',NULL,:PRIORITY). (You'd need the final default clause for thedecodeanyway or all values would go to null.)