4 Replies Latest reply on Nov 28, 2013 8:43 PM by Sergiusz Wolicki-Oracle

    Date format different in R12(MMDDYYYY) compared to 11i(DD-MON-YY) while loading data using sqlldr

    user10088255

      Hi all,

       

      11i -   DB-> 11.2.0.3.0 ; EBS:11.5.10; O/S: SunOS 5.10 Generic_148888-05 sun4u sparc SUNW,SPARC-Enterprise

      R12 - DB-> 11.2.0.3.0 ; EBS: 12.1.3;  O/S: SunOS 5.10 Generic_148888-05 sun4u sparc SUNW,SPARC-Enterprise

       

      Users are facing s strange issue - Date format is different in R12(MMDDYYYY) compared to 11i(DD-MON-YY) while loading data using sqlldr.

       

      11i

       

      SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

      PARAMETER                      VALUE
      ------------------------------ ----------------------------------------
      NLS_LANGUAGE                   AMERICAN
      NLS_TERRITORY                  AMERICA
      NLS_CURRENCY                   $
      NLS_ISO_CURRENCY               AMERICA
      NLS_NUMERIC_CHARACTERS         .,
      NLS_CHARACTERSET               US7ASCII
      NLS_CALENDAR                   GREGORIAN
      NLS_DATE_FORMAT                DD-MON-RR
      NLS_DATE_LANGUAGE              AMERICAN
      NLS_SORT                       BINARY
      NLS_TIME_FORMAT                HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
      NLS_NCHAR_CHARACTERSET         AL16UTF16
      NLS_CSMIG_SCHEMA_VERSION       5
      NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
      NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
      NLS_DUAL_CURRENCY              $
      NLS_SAVED_NCHAR_CS             US7ASCII
      NLS_COMP                       BINARY
      NLS_LENGTH_SEMANTICS           BYTE
      NLS_NCHAR_CONV_EXCP            FALSE
      NLS_RDBMS_VERSION              11.2.0.3.0

       

       

       

      R12

       

      SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

      PARAMETER                      VALUE
      ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
      NLS_LANGUAGE                   AMERICAN
      NLS_TERRITORY                  AMERICA
      NLS_CURRENCY                   $
      NLS_ISO_CURRENCY               AMERICA
      NLS_NUMERIC_CHARACTERS         .,
      NLS_CHARACTERSET               AL32UTF8
      NLS_CALENDAR                   GREGORIAN
      NLS_DATE_FORMAT                DD-MON-RR
      NLS_DATE_LANGUAGE              AMERICAN
      NLS_SORT                       BINARY
      NLS_TIME_FORMAT                HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
      NLS_NCHAR_CHARACTERSET         AL16UTF16
      NLS_CSMIG_SCHEMA_VERSION       5
      NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
      NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
      NLS_DUAL_CURRENCY              $
      NLS_SAVED_NCHAR_CS             US7ASCII
      NLS_DMU_USAGE                  20130930112621,20130928155307,US7ASCII,2
      NLS_COMP                       BINARY
      NLS_LENGTH_SEMANTICS           BYTE
      NLS_NCHAR_CONV_EXCP            FALSE
      NLS_RDBMS_VERSION              11.2.0.3.0

       

       

      Is the data format different due to the different character set in the two databases?

      Could anyone please share the fix to the above issue?

       

      Expected behaviour : We need the same date format in R12 as 11i -> DD-MON-YY.

       

      Thank for your time,

      Regards,