eBS Flexfields and Canonical values



Since I have been working a lot on NUMBER and DATE conversions lately, it’s time to write a short note about canonicals.

Oracle eBS Flexfields (and by extension concurrent program parameters) store all values in a VARCHAR2 column. In general it is a bad idea to store NUMBER and DATE in a VARCHAR2 type. See Datatypes and ORA-1722


But for Flexfields we have a controlled environment. Oracle solves problems related to conversions by storing the NUMBER and DATE format as so called ‘canonicals’. This means that all NUMBER and DATE values are stored in a fixed format. It is up tot he application to guarantee that only values in this canonical format are stored in the Flexfields.


Especially for DFF values, they will often be used in customized software units. These customizations are also responsible for keeping the data in the canonical format. Best practice is to use the packages FND_NUMBER and FND_DATE. This will assure that your customizations don’t break when Oracle changes the canonical format. (e.g. change the precision on NUMBER).



The FND_NUMBER package contains 2 procedures: NUMBER_TO_CANONICAL and CANONICAL_TO_NUMBER. Both take 1 parameter which is a NUMBER respectively a VARCHAR2.

The NUMBER canonical format is ‘FM999999999999999999999.99999999999999999999’. Those are 21 positions left and 20 positions right of the decimal ‘.’. Both functions round the result to 20 decimal positions.

For some reason FND development used a TRANSLATE statement to switch between te decimal separator fort he session and the ‘.’ used in the canonical format. (Hint: TO_NUMBER and TO_CHAR (number) accept the parameter ‘nls_numeric_characters’).


SQL> alter session set nls_numeric_characters=',.';

Session altered.

SQL> select fnd_number.number_to_canonical(to_number('12345,12')) from dual;




SQL> select fnd_number.canonical_to_number('12345.12') from dual;




SQL> alter session set nls_numeric_characters='.,';

Session altered.

SQL> select fnd_number.number_to_canonical(to_number('12345.12')) from dual;






The FND_DATE package contains a lot of procedures for backward compatibility. But for canonical values in Flexfields we need CANONICAL_TO_DATE and DATE_TO_CANONICAL.

Since to TO_CHAR(date) and TO_DATE both accept a date mask, the procedure only contain a TO_CHAR/TO_DATE function.

The canonical mask for DATE is: ‘YYYY/MM/DD HH24:MI:SS’.


SQL> alter session set nls_date_format='DD-MON-YYYY HH:MI:SSAM';

Session altered.

SQL> select fnd_date.date_to_canonical(to_date('5-apr-2017 3:21:45PM','dd-mon-yyyy hh:mi:ssam')) from  dual;



2017/04/05 15:21:45

SQL> select fnd_date.canonical_to_date('2017/02/03 21:32:54') from dual;



03-FEB-2017 09:32:54PM


More goodies

Two other functions that are convenient to use when dealing with string values and dates are STRING_TO_DATE and STRING_TO_CANONICAL.

For dates MONTH, AM/PM and AD/BC values are language dependent. So based on the session settings they may return different results. STRING_TO_DATE and STRING_TO_CANONICAL handle this by trying different language settings. In order they try NLS_DATE_FORMAT (default), NLS_NUMERIC_LANGUAGE and the installed eBS languages from FND_LANGUAGES.

Both take a string that represents a date and the date-mask for the string. STRING_TO_DATE returns a DATE value, while STRING_TO_CANONICAL uses STRING_TO_DATE but converts the result to a canonical VARCHAR2.


SQL> alter session set nls_date_language=AMERICAN;

Session altered.

SQL> select nls_language

2  from fnd_languages

3  where installed_flag in ('B','I');





SQL> select to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

select to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual


ERROR at line 1:

ORA-01843: not a valid month

SQL> select fnd_date.string_to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;



03-MAR-2017 11:45:12PM

SQL> select fnd_date.string_to_canonical('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;



2017/03/03 23:45:12


HTH. As always, feel free to leave comments and questions.

Leave a Reply

Your email address will not be published. Required fields are marked *