Oracle外部表日期字段-在一个数据库中有效，而在另一个数据库中无效 [英] Oracle external table date field - works in one DB and not in another

问题描述

这是一个疯狂的例子:相同的外部表定义合而为一 数据库，但在另一个数据库中失败.不是模式-数据库.两个数据库，都 在同一操作系统上，不同的服务器上.此外，它在第二个日期失败 字段，尽管两者定义相同.两台服务器上的NLS设置相同，以为我认为日期掩码无论如何都应该覆盖它.这是定义:

Here's a crazy one: the same external table definition works fine in one database, but fails in another. Not schema - database. Two databases, both on the same OS, different servers. In addition, it's failing on the 2nd date field, though both are defined the same. The NLS settings are the same on both servers, thought I thought the date mask should override that anyway. Here's the definition:

-- access parameters
-- http://docs.oracle.com/cd/E11882_01/server.112/e16536/et_params.htm

CREATE TABLE ext_tab (
  FIELD1                  VARCHAR2(30),
  FIELD2_DATE             DATE,
  FIELD3                  VARCHAR2(4),
  FIELD4                  VARCHAR2(6),
  FIELD5_DATE             DATE
)
ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR_DATADIR
    ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
        NOBADFILE
        NODISCARDFILE
        LOGFILE 'LOGFILE_LOG'
        FIELDS
          TERMINATED BY ','
          OPTIONALLY ENCLOSED BY '"' and '"'
          LRTRIM
          MISSING FIELD VALUES ARE NULL
          REJECT ROWS WITH ALL NULL FIELDS
          (
  FIELD1                  CHAR(30),
  FIELD2_DATE             CHAR(8)   date_format DATE mask  'YYYYMMDD',
  FIELD3                  CHAR(4),
  FIELD4                  CHAR(6),
  FIELD5_DATE             CHAR(8)   date_format DATE mask  'YYYYMMDD'
          )
      )
    LOCATION ('Sample_Input_csv.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL;

以下是示例数据:

TOTEA01217611,20121122,TOTE,847759,20121122

而且，日志错误:

KUP-04021: field formatting error for field FIELD5_DATE

KUP-04026:字段对于数据类型而言太长了

KUP-04026: field too long for datatype

有人对这种疯狂有答案吗?

Anyone have an answer for this madness?

推荐答案

显然，输入文件已以某种方式损坏，也许是作为二进制而不是ASCII加载的.

Apparently, the input file was corrupt in some way, perhaps loaded as binary instead of ASCII.

我们做了什么: -将另一个文件从第一台服务器拉到第二台服务器并进行了测试-这个文件工作正常！ -删除了第二个文件的内容，并将确切的文本直接从第一个文件剪切并粘贴到了第二个文件中 -再次运行测试-有效！

What we did: - pulled another file from the first server to the second server and tested - this one worked fine! - deleted the contents of this second file, and cut and pasted the exact text from the first file directly into this second file - ran the test again - it worked!

据我们所知，两个文件之间的所有内容都是相同的.为了排除与文件名有关的问题，我们随后将此文件重命名为原始文件的名称，但仍然有效.然后，我们重新通过FTP传输了原始文件，并且这次也可以正常工作.因此，我们唯一能想到的就是文件中有一些非打印字符.

Everything, as far as we could tell, was identical between the two files. To rule out something to do with the filename, we then renamed this file to the original file's name, but it still worked. We then re-FTP'd the original file, and it worked this time as well. So, again, the only thing we can think of is that some non-printing characters were in the file.

我们没有可用的十六进制编辑器进行检查，但是对于遇到相同问题的任何人，以十六进制查看内容将是确保文件中没有任何奇怪内容的一种方法.

We didn't have a hex editor available to check, but for anyone coming across this same thing, viewing the contents as hex would be one way to make sure there was nothing odd in the file.

这篇关于Oracle外部表日期字段-在一个数据库中有效，而在另一个数据库中无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！