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

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

我有一张外桌:

CREATE TABLE PFun ( 
  idF   VARCHAR(255),
  dt   DATE
)
ORGANIZATION EXTERNAL
  (
    TYPE oracle_loader
  DEFAULT DIRECTORY ext_dep    
  ACCESS PARAMETERS
  ( 
    RECORDS DELIMITED BY '\n' 
    BADFILE     CHARACTER
      DISCARDFILE CHARACTER
      LOGFILE     CHARACTER
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL     
      (
        idF     CHAR(255),
        dt      CHAR(255) date_format DATE mask 'YYYY/MM/DD'
      )  
  )
  LOCATION ('prfun.csv')
)
REJECT LIMIT UNLIMITED;

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

/
CREATE OR REPLACE FUNCTION TestFunction(dtIn IN DATE)  
RETURN NUMBER   
IS res NUMBER;
BEGIN  
    SELECT CEIL((TO_DATE(dtIn, 'yyyy/mm/dd') - TO_DATE('2020/12/31', 'yyyy/mm/dd'))/7)
    INTO res
    FROM PFun
    WHERE dtIn >= '2020-01-01';
    RETURN res;
END;
/
SELECT idF, TestFunction(dt)
FROM PFun;

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

ORA-29913: Error executing ODCIEXTTABLEFETCH callout
ORA-01861: the value does not match the format string
ORA-06512: a "SYSTEM.TESTFUNCTION", line 5
29913.0000 - "error in executing% s callout"

* Cause: The execution of the specified callout caused an error.
* 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' )
在函数中尝试以下查询:

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

2w3kk1z52#

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

CREATE FUNCTION TestFunction(dtIn IN DATE) RETURN NUMBER   
IS
BEGIN  
    RETURN TO_NUMBER(TO_CHAR(dtIn, 'WW'));
END;
/

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

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

它将输出:

DTIN      | WEEK_NUM
:-------- | -------:
01-JAN-20 |        1
02-JAN-20 |        1
03-JAN-20 |        1
04-JAN-20 |        1
05-JAN-20 |        1
06-JAN-20 |        1
07-JAN-20 |        1
08-JAN-20 |        2
09-JAN-20 |        2
...
21-DEC-20 |       51
22-DEC-20 |       51
23-DEC-20 |       52
24-DEC-20 |       52
25-DEC-20 |       52
26-DEC-20 |       52
27-DEC-20 |       52
28-DEC-20 |       52
29-DEC-20 |       52
30-DEC-20 |       53
31-DEC-20 |       53

db<>在这里摆弄

相关问题