"ORA-01861: literal does not match format string date" error in Oracle apps concurrent program

"ORA-01861: literal does not match format string date" error in Oracle apps concurrent program

For a long time, I am not able to understand the reason for the occurrence of the above error while using DATE parameters in concurrent programs. Finally, I found the exact reason behind it and thought to share here. Let me explain reason first the cause and then the solution…

 

Cause:

The recent releases of Oracle applications recommend the usage of the data type “FND_STANDARD_DATE” instead of the “DATE” data type. Even if you choose the “DATE” data type, it won’t accept. Hence, while registering the concurrent program parameters we choose “FND_STANDARD_DATE” as the data type for date values.  The format mask for the FND_STANDARD_DATE data type is “YYYY/MM/DD HH24:MI:SS”. As a result, PLSQL procedure will receive a value like ‘2010/12/01 12:00:00’ as the date. Generally, in ORACLE database the “DATE” data type will have the format mask like “DD-MON-YYYY HH24:MI:SS” and this mismatch in the format type is the reason for the error.

“In Simple words, Because of the usage of the data type “FND_STANDARD_DATE”, Oracle Applications send the date value in the format “YYYY/MM/DD HH24:MI:SS” and Oracle database expects the date value in the format “DD-MON-YYYY HH24:MI:SS” and this is the cause of the issue”.

Solution:

Follow the below steps to resolve the issue,

   1.  Define the Date parameters with datatype VARCHAR2 in procedure

   2.  Use FND_DATE.CANONICAL_TO_DATE to convert varchar2 format to oracle date format (DD-MON-YY) and then use it anywhere in a program

Below is an example for the same

CREATE OR REPLACE PROCEDURE APPS.MY_REPORT

               ( p_errbuf     OUT VARCHAR2

                ,p_retcode    OUT VARCHAR2

                ,p_from_date  IN  VARCHAR2

                ,p_to_date    IN  VARCHAR2 )

IS

v_from_date DATE;

v_to_date   DATE;

BEGIN

v_from_date := FND_DATE.CANONICAL_TO_DATE (p_from_date);

v_to_date   := FND_DATE.CANONICAL_TO_DATE (p_to_date);

---

/* Remaining Logic can use the variables v_from_date and v_to_date */

---

END;