Oracle 外部表日期错误

发布于 2024-12-28 11:37:14 字数 3397 浏览 0 评论 0原文

使用外部表将数据加载到 Oracle 11g R2 时出现以下错误。

error processing column DATE_M in row 1 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01847: day of month must be between 1 and last day of month
error processing column DATE_M in row 2 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 3 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 4 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01847: day of month must be between 1 and last day of month
error processing column DATE_M in row 5 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 6 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 7 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 8 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 9 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 10 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month

外部表创建：

CREATE TABLE TABLE_EXT
( "COMPANY" VARCHAR2(101), 
"COMPANY_VN" VARCHAR2(15), 
"IL" VARCHAR2(17), 
"TERMINAL" VARCHAR2(8), 
"T_NO" VARCHAR2(15), 
"NAME" VARCHAR2(108), 
"SNAME" VARCHAR2(50), 
"REF_NO" VARCHAR2(23), 
"AMOUNT" NUMBER(15,2), 
"DATE_M" DATE, 
"TIME" VARCHAR2(11), 
"TEL_NO" VARCHAR2(25), 
"ADDRESS" VARCHAR2(50)
) 
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
COMPANY,
COMPANY_VN,
IL,
TERMINAL,
T_NO,
NAME,
SNAME,
REF_NO,
AMOUNT decimal ,
DATE_M CHAR date_format DATE mask "dd.mm.yyyy" ,
TIME,
TEL_NO,
ADDRESS
)
)
LOCATION ('TABLE_EXT.txt')
)

REJECT LIMIT 10

示例数据：

CITY HOSPITAL|04680072124|CITY|00614860|47746244218|JOHN|WHITE|172871|420,12|21.08.2011|14:26|0806422627784|06
CITY HOSPITAL|04680072124|CITY|00614847|14274017676|BRAD|BROWN|448127|810,00|22.08.2011|11:04|0806427488476|06
CITY HOSPITAL|04680072124|CITY|00614842|16218778886|PETER|BALSON|862626|12,00|24.08.2011|14:16|0806062177008|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|688811|40,10|24.08.2011|08:48|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|460481|42,64|24.08.2011|08:47|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614860|18460662462|JR|TEPE|404622|44,16|22.04.2011|20:08|0806446446866|06
CITY HOSPITAL|04680072124|CITY|00614840|47207688618|BARRY|HRAN|402886|42,40|27.08.2011|11:12|0806478768007|06
CITY HOSPITAL|04680072124|CITY|00614847|42161048612|TOM|HIGGS|148640|12,00|06.06.2011|08:18|0806068076700|06
CITY HOSPITAL|04680072124|CITY|00614846|42161048612|TOM|HIGGS|208847|12,00|06.06.2011|08:46|0806068076700|06

。

NLS_DATE_FORMAT : DD/MM/RRRR

笔记：我尝试了以下方法但没有成功：

DATE_M DATE "dd.mm.yyyy"、

DATE_M CHAR date_format DATE mask "DD/MM/RRRR"

原文

I get the following errors while loading data into Oracle 11g R2 with EXTERNAL TABLES.

error processing column DATE_M in row 1 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01847: day of month must be between 1 and last day of month
error processing column DATE_M in row 2 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 3 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 4 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01847: day of month must be between 1 and last day of month
error processing column DATE_M in row 5 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 6 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 7 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 8 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 9 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 10 for datafile C:\app\S\admin\orcl\dpdump\TABLE_EXT.txt
ORA-01843: not a valid month

External Tables creation:

CREATE TABLE TABLE_EXT
( "COMPANY" VARCHAR2(101), 
"COMPANY_VN" VARCHAR2(15), 
"IL" VARCHAR2(17), 
"TERMINAL" VARCHAR2(8), 
"T_NO" VARCHAR2(15), 
"NAME" VARCHAR2(108), 
"SNAME" VARCHAR2(50), 
"REF_NO" VARCHAR2(23), 
"AMOUNT" NUMBER(15,2), 
"DATE_M" DATE, 
"TIME" VARCHAR2(11), 
"TEL_NO" VARCHAR2(25), 
"ADDRESS" VARCHAR2(50)
) 
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
COMPANY,
COMPANY_VN,
IL,
TERMINAL,
T_NO,
NAME,
SNAME,
REF_NO,
AMOUNT decimal ,
DATE_M CHAR date_format DATE mask "dd.mm.yyyy" ,
TIME,
TEL_NO,
ADDRESS
)
)
LOCATION ('TABLE_EXT.txt')
)

REJECT LIMIT 10

Sample Data:

CITY HOSPITAL|04680072124|CITY|00614860|47746244218|JOHN|WHITE|172871|420,12|21.08.2011|14:26|0806422627784|06
CITY HOSPITAL|04680072124|CITY|00614847|14274017676|BRAD|BROWN|448127|810,00|22.08.2011|11:04|0806427488476|06
CITY HOSPITAL|04680072124|CITY|00614842|16218778886|PETER|BALSON|862626|12,00|24.08.2011|14:16|0806062177008|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|688811|40,10|24.08.2011|08:48|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|460481|42,64|24.08.2011|08:47|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614860|18460662462|JR|TEPE|404622|44,16|22.04.2011|20:08|0806446446866|06
CITY HOSPITAL|04680072124|CITY|00614840|47207688618|BARRY|HRAN|402886|42,40|27.08.2011|11:12|0806478768007|06
CITY HOSPITAL|04680072124|CITY|00614847|42161048612|TOM|HIGGS|148640|12,00|06.06.2011|08:18|0806068076700|06
CITY HOSPITAL|04680072124|CITY|00614846|42161048612|TOM|HIGGS|208847|12,00|06.06.2011|08:46|0806068076700|06

NLS_DATE_FORMAT : DD/MM/RRRR

NOTE:
I tried the following to no avail :

DATE_M DATE "dd.mm.yyyy" ,

DATE_M CHAR date_format DATE mask "DD/MM/RRRR"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鹿港巷口少年归 2025-01-04 11:37:14

首先，您需要在单引号内指定日期格式掩码，而不是双引号。

其次，这相当令人困惑，您为“AMOUNT”列指定的格式不正确。当您说数据是“DECIMAL”时，您实际上是在输入文件中指定二进制格式。您应该在访问参数中为FIELD 定义指定字符，并让Oracle 将字段转换为数字。我很少（如果有的话）使用除 CHAR 之外的任何格式来描述 SQL*Loader 或外部表中的输入数据。然而，也就是说，您有点卡在这里，因为我认为您无法在外部表定义中指定小数字符（在您的情况下为“，”）。您只能通过 NLS 实例参数来更改它。

这是我为使您的示例正常工作而所做的（注意：10g 数据库）：

CREATE TABLE TABLE_EXT ( "COMPANY" VARCHAR2(101)
                      ,  "COMPANY_VN" VARCHAR2(15)
                      ,  "IL" VARCHAR2(17)
                      ,  "TERMINAL" VARCHAR2(8)
                      ,  "T_NO" VARCHAR2(15)
                      ,  "NAME" VARCHAR2(108)
                      ,  "SNAME" VARCHAR2(50)
                      ,  "REF_NO" VARCHAR2(23)
                      ,  "AMOUNT" NUMBER(15,2)
                      ,  "DATE_M" DATE
                      ,  "TIME_M" VARCHAR2(11)
                      ,  "TEL_NO" VARCHAR2(25)
                      ,  "ADDRESS" VARCHAR2(50) )
        ORGANIZATION EXTERNAL 
          ( TYPE ORACLE_LOADER 
            DEFAULT DIRECTORY external_tables 
            ACCESS PARAMETERS 
               ( RECORDS DELIMITED BY NEWLINE 
                  FIELDS TERMINATED BY '|' 
                  MISSING FIELD VALUES ARE NULL 
                 ( COMPANY
                 , COMPANY_VN
                 , IL
                 , TERMINAL
                 , T_NO
                 , NAME
                 , SNAME
                 , REF_NO
                 , AMOUNT 
                 , DATE_M CHAR date_format DATE mask 'dd.mm.yyyy'
                 , TIME_M
                 , TEL_NO
                 , ADDRESS ) ) 
            LOCATION ('TABLE_EXT.txt') )  
            REJECT LIMIT 10 ;

我稍微修改了您的数据，将您的十进制字符更改为“.”数据中：

CITY HOSPITAL|04680072124|CITY|00614860|47746244218|JOHN|WHITE|172871|420.12|21.08.2011|14:26|0806422627784|06
CITY HOSPITAL|04680072124|CITY|00614847|14274017676|BRAD|BROWN|448127|810.00|22.08.2011|11:04|0806427488476|06
CITY HOSPITAL|04680072124|CITY|00614842|16218778886|PETER|BALSON|862626|12.00|24.08.2011|14:16|0806062177008|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|688811|40.10|24.08.2011|08:48|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|460481|42.64|24.08.2011|08:47|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614860|18460662462|JR|TEPE|404622|44,16|22.04.2011|20:08|0806446446866|06
CITY HOSPITAL|04680072124|CITY|00614840|47207688618|BARRY|HRAN|402886|42.40|27.08.2011|11:12|0806478768007|06
CITY HOSPITAL|04680072124|CITY|00614847|42161048612|TOM|HIGGS|148640|12.00|06.06.2011|08:18|0806068076700|06
CITY HOSPITAL|04680072124|CITY|00614846|42161048612|TOM|HIGGS|208847|12.00|06.06.2011|08:46|0806068076700|06

这对我有用。

最重要的是，我认为您需要（1）修改数据，（2）修改实例 NLS 参数以更改小数字符或（3）将数据作为字符加载并在顶部的视图中使用 TO_NUMBER 函数外部表。

First, you need to specify your date format mask inside single quotes, not double quotes.

Second, and this is fairly confusing, the format you specify for the "AMOUNT" column is incorrect. When you say the data is "DECIMAL", you are actually specifying a binary format in the input file. You should specify character for the FIELD definitions in the access parameters, and let Oracle convert the field to numeric. I very rarely (if ever) use any format but CHAR to describe the input data to either SQL*Loader or in external tables. That said, however, you're kind of stuck here because I don't think that you can specify the decimal character (',' in your case) in the external table definition. You can only change it via NLS instance parameters.

Here's what i did (note: 10g database) to make your example work:

CREATE TABLE TABLE_EXT ( "COMPANY" VARCHAR2(101)
                      ,  "COMPANY_VN" VARCHAR2(15)
                      ,  "IL" VARCHAR2(17)
                      ,  "TERMINAL" VARCHAR2(8)
                      ,  "T_NO" VARCHAR2(15)
                      ,  "NAME" VARCHAR2(108)
                      ,  "SNAME" VARCHAR2(50)
                      ,  "REF_NO" VARCHAR2(23)
                      ,  "AMOUNT" NUMBER(15,2)
                      ,  "DATE_M" DATE
                      ,  "TIME_M" VARCHAR2(11)
                      ,  "TEL_NO" VARCHAR2(25)
                      ,  "ADDRESS" VARCHAR2(50) )
        ORGANIZATION EXTERNAL 
          ( TYPE ORACLE_LOADER 
            DEFAULT DIRECTORY external_tables 
            ACCESS PARAMETERS 
               ( RECORDS DELIMITED BY NEWLINE 
                  FIELDS TERMINATED BY '|' 
                  MISSING FIELD VALUES ARE NULL 
                 ( COMPANY
                 , COMPANY_VN
                 , IL
                 , TERMINAL
                 , T_NO
                 , NAME
                 , SNAME
                 , REF_NO
                 , AMOUNT 
                 , DATE_M CHAR date_format DATE mask 'dd.mm.yyyy'
                 , TIME_M
                 , TEL_NO
                 , ADDRESS ) ) 
            LOCATION ('TABLE_EXT.txt') )  
            REJECT LIMIT 10 ;

I modified your data slightly to change your decimal character to a '.' in the data:

CITY HOSPITAL|04680072124|CITY|00614860|47746244218|JOHN|WHITE|172871|420.12|21.08.2011|14:26|0806422627784|06
CITY HOSPITAL|04680072124|CITY|00614847|14274017676|BRAD|BROWN|448127|810.00|22.08.2011|11:04|0806427488476|06
CITY HOSPITAL|04680072124|CITY|00614842|16218778886|PETER|BALSON|862626|12.00|24.08.2011|14:16|0806062177008|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|688811|40.10|24.08.2011|08:48|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|460481|42.64|24.08.2011|08:47|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614860|18460662462|JR|TEPE|404622|44,16|22.04.2011|20:08|0806446446866|06
CITY HOSPITAL|04680072124|CITY|00614840|47207688618|BARRY|HRAN|402886|42.40|27.08.2011|11:12|0806478768007|06
CITY HOSPITAL|04680072124|CITY|00614847|42161048612|TOM|HIGGS|148640|12.00|06.06.2011|08:18|0806068076700|06
CITY HOSPITAL|04680072124|CITY|00614846|42161048612|TOM|HIGGS|208847|12.00|06.06.2011|08:46|0806068076700|06

This worked for me.

Bottom line, I think you'll need to (1)modify your data, (2) modify your instance NLS parameters to change the decimal character or (3) load your data as character and use the TO_NUMBER function in a view on top of the external table.

回复收藏 0 原文

~没有更多了~