ORA-01861: the value does not match the format string

I have this External Table:

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;

I created this function to return the week number of the year

/
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;

If I only run the function it tells me that everything has been compiled. But when i run the SELECT it gives me the following error:

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.

[I enter the dates like this: ‘2020/01/01′ but sql-developer on the control query shows them to me like this ’01-JAN-20’. I don’t know if this info is important]

How I fix that?

Answer

You are converting a date field into again date which seems to be throwing an error.

Also, You are trying to compare date field dtIn with string. The string should be converted to date before comparing with the date filed (dtIn >= '2020-01-01')

Try the following query in your function:

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