错误ORA-01841:在文件中传递出生日期时

Question

我在一个文件中传递"19851003"作为出生日期，但我在日志文件中得到以下错误。

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

有没有人知道为什么当我把它作为一个字符串传递时，会出现错误?

日志文件错误:

error processing column DATE_OF_BIRTH in row 2 for datafile /xfer/x/directory/filename.csv
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

我的表代码:

CREATE TABLE table_name
(
  ID                  VARCHAR2(10 BYTE),      
  FIRST_NAME          VARCHAR2(150 BYTE),
  LAST_NAME           VARCHAR2(150 BYTE),
  MIDDLE_NAME         VARCHAR2(150 BYTE),
  DATE_OF_BIRTH       DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DIRECTORY_TABLES
  ACCESS PARAMETERS 
  (RECORDS  DELIMITED BY 'n'
   SKIP 1
   BADFILE  DIRECTORY_TABLES:'filelist.bad'
   LOGFILE  DIRECTORY_TABLES:'filelist.log'
   DISCARDFILE   DIRECTORY_TABLES:'filelist.dsc'
   FIELDS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '"'
   MISSING FIELD VALUES ARE NULL
   REJECT ROWS WITH ALL NULL FIELDS
   (ID                  ,
    FIRST_NAME          ,
    LAST_NAME           ,
    MIDDLE_NAME         ,
    DATE_OF_BIRTH       DATE "YYYYMMDD" 
   )
)
LOCATION (DIRECTORY_TABLES:'filename.csv')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

我的CSV文件:

"id","First Name","Last Name","MI","DOB"
"2","Felicia","Brown","M","19810209"
"3","Jose","Romero","M","19890706"
"4","Valerie","Moore","","19621112"

Answer 1

在DATE_OF_BIRTH的规范中需要一个日期格式规范。试一试:

DATE_OF_BIRTH  CHAR DATE_FORMAT DATE MASK "YYYYMMDD"

参考这里，然后搜索"date_format_spec"

分享快乐。

Answer 2

19851003不像错误提示的那样介于-4713和9999之间。

我怀疑你想通过1985年作为年份，你不小心传递了一个完整的日期(yyyyMmdd)