ora-01861:值与格式字符串不匹配

dl5txlt9  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(455)

我有一张外桌:

  1. CREATE TABLE PFun (
  2. idF VARCHAR(255),
  3. dt DATE
  4. )
  5. ORGANIZATION EXTERNAL
  6. (
  7. TYPE oracle_loader
  8. DEFAULT DIRECTORY ext_dep
  9. ACCESS PARAMETERS
  10. (
  11. RECORDS DELIMITED BY '\n'
  12. BADFILE CHARACTER
  13. DISCARDFILE CHARACTER
  14. LOGFILE CHARACTER
  15. FIELDS TERMINATED BY ','
  16. OPTIONALLY ENCLOSED BY "'"
  17. MISSING FIELD VALUES ARE NULL
  18. (
  19. idF CHAR(255),
  20. dt CHAR(255) date_format DATE mask 'YYYY/MM/DD'
  21. )
  22. )
  23. LOCATION ('prfun.csv')
  24. )
  25. REJECT LIMIT UNLIMITED;

我创建这个函数是为了返回一年中的周数

  1. /
  2. CREATE OR REPLACE FUNCTION TestFunction(dtIn IN DATE)
  3. RETURN NUMBER
  4. IS res NUMBER;
  5. BEGIN
  6. SELECT CEIL((TO_DATE(dtIn, 'yyyy/mm/dd') - TO_DATE('2020/12/31', 'yyyy/mm/dd'))/7)
  7. INTO res
  8. FROM PFun
  9. WHERE dtIn >= '2020-01-01';
  10. RETURN res;
  11. END;
  12. /
  13. SELECT idF, TestFunction(dt)
  14. FROM PFun;

如果我只运行这个函数,它会告诉我所有的东西都已经编译好了。但是当我运行select时,它会给我以下错误:

  1. ORA-29913: Error executing ODCIEXTTABLEFETCH callout
  2. ORA-01861: the value does not match the format string
  3. ORA-06512: a "SYSTEM.TESTFUNCTION", line 5
  4. 29913.0000 - "error in executing% s callout"
  5. * Cause: The execution of the specified callout caused an error.
  6. * Action: Examine the error messages take appropriate action.

[我这样输入日期:'2020/01/01',但是sql developer在控制查询中向我显示的日期是'01-jan-20'。我不知道这些信息是否重要]
我该怎么解决?

62lalag4

62lalag41#

您正在将日期字段转换为似乎抛出错误的日期。
同时,你也在尝试比较 date 字段 dtInstring . 这个 string 应转换为 date 与备案日期比较前( dtIn >= '2020-01-01' )
在函数中尝试以下查询:

  1. SELECT CEIL(dtIn - TO_DATE('2020/12/31', 'yyyy/mm/dd'))/7) -- to_Date is not needed for dtIn
  2. INTO res
  3. FROM PFun
  4. WHERE dtIn >= date '2020-01-01' -- this should be date so made it date using date literal
2w3kk1z5

2w3kk1z52#

只是使用 TO_CHAR( your_date, 'WW' ) 返回周数:

  1. CREATE FUNCTION TestFunction(dtIn IN DATE) RETURN NUMBER
  2. IS
  3. BEGIN
  4. RETURN TO_NUMBER(TO_CHAR(dtIn, 'WW'));
  5. END;
  6. /

(而且您可能不需要为一些简单且可以直接使用的东西编写函数 TO_NUMBER(TO_CHAR(your_column, 'WW')) 代替函数调用。)
那么,今年:

  1. SELECT DATE '2019-12-31' + LEVEL AS dtIn,
  2. TestFunction( DATE '2019-12-31' + LEVEL ) AS week_num
  3. FROM DUAL
  4. CONNECT BY LEVEL <= 366

它将输出:

  1. DTIN | WEEK_NUM
  2. :-------- | -------:
  3. 01-JAN-20 | 1
  4. 02-JAN-20 | 1
  5. 03-JAN-20 | 1
  6. 04-JAN-20 | 1
  7. 05-JAN-20 | 1
  8. 06-JAN-20 | 1
  9. 07-JAN-20 | 1
  10. 08-JAN-20 | 2
  11. 09-JAN-20 | 2
  12. ...
  13. 21-DEC-20 | 51
  14. 22-DEC-20 | 51
  15. 23-DEC-20 | 52
  16. 24-DEC-20 | 52
  17. 25-DEC-20 | 52
  18. 26-DEC-20 | 52
  19. 27-DEC-20 | 52
  20. 28-DEC-20 | 52
  21. 29-DEC-20 | 52
  22. 30-DEC-20 | 53
  23. 31-DEC-20 | 53

db<>在这里摆弄

展开查看全部

相关问题