Oracle sqlldr:此处不允许使用列 [英] Oracle sqlldr: column not allowed here

问题描述

有人可以发现此尝试的数据加载中的错误吗? '\\N'是因为这是从mysql导入的OUTFILE转储，将\ N表示NULL字段.

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的情况.

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

在Linux上使用Oracle 10g.

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')
)

样本数据(记录1). ^ _代表不可打印的0x1F分隔符.

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

错误:

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

推荐答案

BLANKS是SQL * Loader关键字，您不能在decode SQL语句中使用它-它会将其视为列名.如果它确实是一个空(零长度)字符串(如分隔文件中的情况)，则在decode中，您可以使用''而不是BLANKS；但是Oracle还是将其视为null.在这种情况下，decode应该是多余的，您可以像使用其他列一样使用NULLIF.如果空"字符串实际上是一个或多个空格，则可以执行类似decode(TRIM(:PRIORITY),'',NULL,'\\N',NULL,:PRIORITY)的操作. (无论如何，您都需要decode的最终默认子句，否则所有值都将为null.)

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.)

这篇关于Oracle sqlldr:此处不允许使用列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！