Source : Oracle 11g NLS_DATE_FORMAT– DD-MM-YYYY
Target : Oracle 19c NLS_DATE_FORMAT– DD-MON-RR
Migration Pattern : New empty 19c Database is created followed by exporting and importing of application objects and data from Oracle 11g database into 19c database.
Issues :
Issues : Packages failing with ‘ Not a valid Month’ – Assigning a DATE to a VARCHAR2 and VARCHAR2 to Date without formatting. Runtime errors which need fix at the package level.
Data Issue – TO_DATE format applied on DATE fields, due to this usage, the year getting stored as ‘0022’ instead of ‘ ‘2022’ – Possible data issues
Application Extract Issues – As the NLS_date_format got changed , the date fields when extracted to a csv file, the format is printed as ‘DD-MON-YY’ instead of ‘DD_MM_YYYY’. – As expected, due to NLS_FORMAT changes
Request :
What are the implications of changing the NLS_DATE_FORMAT, since most of the issues are run time errors, The identified issues are mentioned in the top?
Reference to whitepapers to formally check areas of impact due to NLS_DATE_FORMAT changes ?
What will be the impact of changing the NLS_DATE_FORMAT back to DD-MM-YYYY as it was originally in oracle 11g?
Supported reference white papers/documents/Incidents changing/reversing the NLS_DATE_FORMAT on a running production environment ?
We have multiple container databases where 3 PDB’s has DD-MON-RR and remaining PDB’s has DD-MM-YYYY, will there be any impact on date format issues when transferring data between databases with different Date formats using DB Links ?