CODEWITHSUNDEEP
sqloracle-databasevalidation
heathcliff1927
heathcliff1927

Reputation: 15

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?

Upvotes: 0

Views: 305

Answers (2)

MT0
MT0

Reputation: 168671

Just use TO_CHAR( your_date, 'WW' ) to return the week number:

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

(And you probably don't need to write a function for something that simple and could just use TO_NUMBER(TO_CHAR(your_column, 'WW')) in place of the function call.)

Then, for this year:

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

It will output:

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<>fiddle here

Upvotes: 0

Popeye
Popeye

Reputation: 35930

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

Upvotes: 1

Related Questions