Wednesday, December 1, 2010

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

3 Responses to “"ORA-01861: literal does not match format string date" error in Oracle apps concurrent program”

Anonymous said...
June 11, 2012 at 8:54 AM

Although your solution is more or less correct, I would suggest making a clear distinction between "data type", "format", and "value set".

SQL expressions such as database table column values and PL/SQL procedure parameters a data type. This can be VARCHAR2 or DATE for example.

If you have a procedure parameter that should be interpreted as a date, but has the data type VARCHAR2, you will need to use a conversion format when converting it to an expression of data type DATE.

If you pass a VARCHAR2 expression to a DATE procedure parameter it will be converted implicitly for you (usually using the NLS_DATE_FORMAT format).

Concurrent program parameters are in effect always expressions with data type VARCHAR2.

A value set is used to code a concurrent program parameter (VARCHAR2 expression) in a well defined format. For parameters representing dates it is important to use a format that is NLS language and territory independent, and preferably has a representation for the time portion too. That is what the canonical format is for.

Use the FND_STANDARD_DATE value set to pass in a VARCHAR2 that should represent a date. Obtain the date expression as follows:
l_date DATE := fnd_date.canonical_to_date(p_date);


Team search said...
June 13, 2012 at 8:47 PM

Hi ....

Thank you for sharing your knowledge to our Audiences.


Anonymous said...
April 17, 2013 at 1:25 AM

Thank you. I've been beating myself up over this.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.