英文:
While loading the csv file into table using control file, am facing some date format issue. can anyone help me on this?
问题
加载数据
替换
到表 Tab_A
字段由","分隔，可选地包含在'"'中
尾随 NULLCOLS
(
项目 "trim(:ITEM)",
位置 "trim(:LOC)",
日期 "TO_DATE(:DATE,'DD-MM-YYYY')",
持续时间,
数量
当我执行ctl文件时，出现以下问题。
> 记录10001：拒绝 - 表tab_T，列DATE的错误。<BR>
> ORA-01841：(full) year must be between -4713 and +9999, and not be 0
<details>
<summary>英文:</summary>
LOAD DATA
REPLACE
INTO TABLE Tab_A
Fields terminated by "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ITEM "trim(:ITEM)",
LOC "trim(:LOC)",
DATE "TO_DATE(:DATE,'DD-MM-YYYY')",
DUR,
QTY
When i execute the ctl file, am facing the below issue.
> Record 10001: Rejected - Error on table tab_T, column DATE.<BR>
> ORA-01841: (full) year must be between -4713 and +9999, and not be 0
</details>
# 答案1
**得分**: 1
你的日期列中存在错误数据，这些数据可能来自一个VARCHAR列，因此可能包含任何内容。你需要运行一些查询来查找它（也称为合理性检查）。查找与预期格式或值不匹配的数据。
更正！你的源数据中存在错误的日期格式。为了找到它，可以将其加载到一个临时表中，其中日期列是VARCHAR2(10)（直接加载），然后运行正则表达式来查找不匹配格式的值：
```sql
select item, loc, date
from Tab_A_stg
where not regexp_like(date, '^(0?[1-9]|[12][0-9]|3[01])-(0?[1-9]|1[012])-(19|20)\d\d$');
这将查找日期不符合标准DD-MM-YYYY格式的行。
英文:
You have bad data in your date column, which must be coming from a VARCHAR column and thus could contain anything. You'll need to run some queries to find it (also known as sanity checks). Look for data that does not match the expected formats or values.
Correction! You have a bad date format in the source data you are loading. To find it, maybe load it into a staging table where the date column is a VARCHAR2(10) (load it straight in), then run a regex against it to find values that don't match the format:
select item, loc, date
from Tab_A_stg
where not regexp_like(date, '^(0?[1-9]|[12][0-9]|3[01])-(0?[1-9]|1[012])-(19|20)\d\d$');
This will find rows where the date does not match a standard DD-MM-YYYY date.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论