1 2 Previous Next 20 Replies Latest reply: May 12, 2011 10:21 PM by Hemant K Chitale RSS

    What character should I set for the target database?

    548150
      What character should I set for the target database?
      what NLS should I set for target database?
      I imp data from the .dmp file and get the following error message:
      Export file created by EXPORT:V10.02.01 via conventional path
      import done in WE8MSWIN1252 character set and AL16UTF16
         NCHAR character set
      export client uses US7ASCII character set (possible charset conversion)
      export server uses UTF8 NCHAR character set (possible 
         ncharset conversion)
      ...
      IMP-00003: ORACLE error 1861 encountered
      ORA-01861: literal does not match format string
        • 1. Re: What character should I set for the target database?
          sb92075
          01861, 00000, "literal does not match format string"
          // *Cause:  Literals in the input must be the same length as literals in
          //  the format string (with the exception of leading whitespace).  If the
          //  "FX" modifier has been toggled on, the literal must match exactly,
          //  with no extra whitespace.
          // *Action: Correct the format string to match the literal.
          Re: What character should I set for the target database?
          When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

          Edited by: sb92075 on May 11, 2011 8:23 PM
          • 2. Re: What character should I set for the target database?
            548150
            From the output, you can see the NSL set on the exp and imp servers don't match. Is it a problem?
            • 3. Re: What character should I set for the target database?
              sb92075
              Handle:      jetq
              Status Level:      Pro (725)
              Registered:      Nov 24, 2006
              Total Posts:      1,031
              Total Questions:      152 (122 unresolved)
              so many questions & so few answers.
              :-(
              From the output, you can see the NSL set on the exp and imp servers don't match. Is it a problem?
              What is displayed may indicate a problem, BUT the error code/message has nothing to do with characterset.

              A characterset incompatibility might garble some characters, but won't throw the error you have.

              You have not posted enough detail to give me a reasonable starting point to guess root cause.
              • 4. Re: What character should I set for the target database?
                Hemant K Chitale
                You should create the new database in the correct characterset.

                However, the ORA 1861 error relates to the Date Format. You likely haven't set the NLS_DATE_FORMAT in your import session environment to match the date format used at the exported database.


                Hemant K Chitale
                • 5. Re: What character should I set for the target database?
                  orafad
                  jetq wrote:
                  What character should I set for the target database?
                  what NLS should I set for target database?
                  How should we know? Not sure what you mean though.
                  I imp data from the .dmp file and get the following error message:
                  ...
                  export client uses US7ASCII character set (possible charset conversion)
                  Please post heading lines from export log.

                  And post nls_characterset from source database. Target likely has WE8MSWIN1252 (from imp session log), correct?

                  What languages (alphabets) should the database support?

                  IMP-00003: ORACLE error 1861 encountered
                  ORA-01861: literal does not match format string
                  The language and territory parts of NLS_LANG also affect the import session. Date format string setting, if not set otherwise (as suggested above), is "inherited" from defaults provided by territory part of NLS_LANG. That's simplified. But NLS parameters and behaviour is of course well documented, so you could/should reference the docs.

                  For more specific NLS discussions there's the dedicated forum: {forum:id=50}.

                  Edited by: orafad on May 12, 2011 9:29 AM
                  • 6. Re: What character should I set for the target database?
                    548150
                    Connected to: Oracle Database 10g Enterprise Edition Release 
                    10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
                    
                    Export file created by EXPORT:V10.02.01 via conventional path
                    import done in WE8MSWIN1252 character set and AL16UTF16 
                    NCHAR character set
                    export client uses US7ASCII character set (possible charset 
                    conversion)
                    export server uses UTF8 NCHAR character set (possible 
                    ncharset conversion)
                    . importing SYSTEM's objects into SYSTEM
                    ...
                    IMP-00003: ORACLE error 1861 encountered
                    ORA-01861: literal does not match format string
                    . . importing table    "ACC_POSITIVE_NEGATIVE_ACL"          
                    0 rows imported
                    IMP-00017: following statement failed with ORACLE error 1861:
                     " ALTER TABLE "ACC_USER_PROFILE" MODIFY ("UP_EXPIRY_DATE" 
                    DEFAULT '30000101'"
                     " )"
                    Edited by: jetq on May 12, 2011 8:44 AM
                    • 7. Re: What character should I set for the target database?
                      548150
                      SQL> show parameter nls_date_format;
                      
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- ------------
                      nls_date_format                      string
                      
                      SQL> select sysdate from dual;
                      
                      SYSDATE
                      ---------
                      12-MAY-11
                      
                      SQL> 
                      Edited by: jetq on May 12, 2011 8:47 AM
                      • 8. Re: What character should I set for the target database?
                        Hemant K Chitale
                        The date format in the dump file is very different :

                        " ALTER TABLE "ACC_USER_PROFILE" MODIFY ("UP_EXPIRY_DATE" DEFAULT '30000101'")

                        I can't figure out what it is !
                        But it isn't DD-MON-RR

                        Hemant K Chitale
                        • 9. Re: What character should I set for the target database?
                          sybrand_b
                          Apparently it is YYYMMDD, as the value look to me like 'plus infinite'
                          I once had an important fail because of a different date mask, where the default on the client, was different from the default on the server.
                          Yet another reason to use to_date always!

                          ----------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 10. Re: What character should I set for the target database?
                            548150
                            Thank you all.

                            Now I resintall the database with UTF8 character set. Before imp, I change the client NSL_LANG value. But also get same error message except the with different character set specification:


                            C:\Install\export>set nsl_lang=america_canada.us7acii
                            C:\Install\export>imp SYSTEM/metex_01 FILE=dry.dmp log=imp_log_3.log full=y
                            
                            Import: Release 10.2.0.1.0 - Production on Thu May 12 12:08:18 2011
                            
                            Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                            
                            
                            Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                            With the Partitioning, OLAP and Data Mining options
                            
                            Export file created by EXPORT:V10.02.01 via conventional path
                            import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
                            import server uses UTF8 character set (possible charset conversion)
                            export client uses US7ASCII character set (possible charset conversion)
                            . importing SYSTEM's objects into SYSTEM
                            . importing DRY's objects into DRY
                            IMP-00003: ORACLE error 1435 encountered
                            ORA-01435: user does not exist
                            IMP-00015: following statement failed because the object already exists:
                             "CREATE PUBLIC SYNONYM "XMLTYPE" FOR "SYS"."XMLTYPE""
                            Import terminated successfully with warnings.
                            Some error message from log file:
                            IMP-00017: following statement failed with ORACLE error 1861:
                             " ALTER TABLE "BENEFIT_LIST" MODIFY ("REC_TERMINATION_DT" DEFAULT '30000101'"
                             " )"
                            IMP-00003: ORACLE error 1861 encountered
                            ORA-01861: literal does not match format string
                            IMP-00017: following statement failed with ORACLE error 1861:
                             " ALTER TABLE "BENEFIT_LIST_RULE" MODIFY ("REC_TERMINATION_DT" DEFAULT '3000"
                             "0101' )"
                            IMP-00003: ORACLE error 1861 encountered
                            ORA-01861: literal does not match format string
                            . . importing table         "BENEFIT_LIST_RULE_CD"         11 rows imported
                            IMP-00017: following statement failed with ORACLE error 1861:
                             " ALTER TABLE "BENEFIT_MANAGER" MODIFY ("REC_TERMINATION_DT" DEFAULT '300001"
                             "01' )"
                            Edited by: jetq on May 12, 2011 12:31 PM
                            • 11. Re: What character should I set for the target database?
                              sb92075
                              But also get same error message except the with different character set specification:
                              same as WHAT?
                              • 12. Re: What character should I set for the target database?
                                548150
                                IMP-00003: ORACLE error 1861 encountered
                                ORA-01861: literal does not match format string
                                • 13. Re: What character should I set for the target database?
                                  sb92075
                                  SLOW learner?

                                  below from previous response
                                  the error code/message has nothing to do with characterset.
                                  • 14. Re: What character should I set for the target database?
                                    548150
                                    Now I think you are right.
                                    How to deal with it?
                                    Change the NLS_date_format?
                                    Thank you
                                    1 2 Previous Next