1 2 Previous Next 15 Replies Latest reply: Dec 17, 2012 6:04 AM by rthampi RSS

    Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125

    rthampi
      Hi guys
      While trying to figure out the issues with connecting Developer 6i with Oracle 11g r2, I got the following details against 11g R2, 10g 2.3 databases
      11g
      
      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_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY     $
      NLS_COMP     BINARY
      NLS_LENGTH_SEMANTICS     BYTE
      NLS_NCHAR_CONV_EXCP     FALSE
      NLS_NCHAR_CHARACTERSET     AL16UTF16
      NLS_RDBMS_VERSION     11.2.0.1.0
      and for 10g database the following
      PARAMETER     VALUE
      NLS_LANGUAGE     AMERICAN
      NLS_TERRITORY     AMERICA
      NLS_CURRENCY     $
      NLS_ISO_CURRENCY     AMERICA
      NLS_NUMERIC_CHARACTERS     .,
      *NLS_CHARACTERSET     WE8MSWIN1252*
      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_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY     $
      NLS_COMP     BINARY
      NLS_LENGTH_SEMANTICS     BYTE
      NLS_NCHAR_CONV_EXCP     FALSE
      NLS_NCHAR_CHARACTERSET     AL16UTF16
      NLS_RDBMS_VERSION     10.2.0.3.0
      I altered the character set with

      ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;

      and now I can connect developer 6i with 11g without any troubles. Now, please tell me what kind of security issues/system wide issues I invite by such a change.

      Regards,

      raj
        • 1. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
          Srini Chavali-Oracle
          rthampi wrote:
          Hi guys
          While trying to figure out the issues with connecting Developer 6i with Oracle 11g r2, I got the following details against 11g R2, 10g 2.3 databases
          Pl elaborate on what the issues are - any error messages or codes ? Is there a reason for continuing to use desupported version of Developer ?
          11g
          
          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_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
          NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
          NLS_DUAL_CURRENCY     $
          NLS_COMP     BINARY
          NLS_LENGTH_SEMANTICS     BYTE
          NLS_NCHAR_CONV_EXCP     FALSE
          NLS_NCHAR_CHARACTERSET     AL16UTF16
          NLS_RDBMS_VERSION     11.2.0.1.0
          and for 10g database the following
          PARAMETER     VALUE
          NLS_LANGUAGE     AMERICAN
          NLS_TERRITORY     AMERICA
          NLS_CURRENCY     $
          NLS_ISO_CURRENCY     AMERICA
          NLS_NUMERIC_CHARACTERS     .,
          *NLS_CHARACTERSET     WE8MSWIN1252*
          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_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
          NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
          NLS_DUAL_CURRENCY     $
          NLS_COMP     BINARY
          NLS_LENGTH_SEMANTICS     BYTE
          NLS_NCHAR_CONV_EXCP     FALSE
          NLS_NCHAR_CHARACTERSET     AL16UTF16
          NLS_RDBMS_VERSION     10.2.0.3.0
          I altered the character set with

          ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
          This is an unsupported command and will cause your database to be corrupted beyond repair. Hope you have a backup of your database that you can recover from.

          https://forums.oracle.com/forums/search.jspa?threadID=&q=INTERNAL_USE&objID=c18&dateRange=all&userID=&numResults=15

          The methods to change the characterset of the database are documented

          http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm

          Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]
          and now I can connect developer 6i with 11g without any troubles. Now, please tell me what kind of security issues/system wide issues I invite by such a change.

          Regards,

          raj
          HTH
          Srini
          • 2. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
            Osama_Mustafa
            Read MOS Notes :
            Which Character Set Supports Which Language [ID 62421.1]
            • 3. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
              rthampi
              Thank you Srini, for the detailed explanation :) Btw, my 11g database is for lab test and brand new. Ie, fresh install and I followed the standard commands (I believe) to change the character set, as I wanted to provide a solution for connectivity issues between developer 6i and 11g databases in general.

              SHUTDOWN IMMEDIATE;
              STARTUP MOUNT;
              ALTER SYSTEM ENABLE RESTRICTED SESSION;
              ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
              ALTER SYSTEM SET AQ_TM_PROCESSES=0;
              ALTER DATABASE OPEN;
              ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
              ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252; --10g
              ALTER DATABASE CHARACTER SET INTERNAL_USE AR8ISO8859P6;--r12 with arabic support
              SHUTDOWN;
              STARTUP RESTRICT;
              SHUTDOWN;
              STARTUP; 
              Sreeni, now I have one question:
              We are using Oracle R12 (db 10.2.0.3) and with arabic support the character set is AR8ISO8859P6. Hundreds of reports were developed using Reports 6i and we are planning to migrate to 11g as a part of our application upgrade process.
              I lead the internal development team and we cannot migrate all existing reports to 10g reports immediately, due to multiple reasons. Hence all possibilities to get the developer 6i reports must be tried

              Now, the question is, I already changed the 11g database character set to AR8ISO8859P6, and developer 6i reports/forms are getting connected to the database and working as expected, without breaking up at point. Please let me know something, as we are migrating from a database which is already on AR8ISO8859P6 to a newer database with AR8ISO8859P6 could cause problems as mentioned with your post?

              regards,

              raj
              • 4. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                Srini Chavali-Oracle
                rthampi wrote:
                Thank you Srini, for the detailed explanation :) Btw, my 11g database is for lab test and brand new. Ie, fresh install and I followed the standard commands (I believe) to change the character set, as I wanted to provide a solution for connectivity issues between developer 6i and 11g databases in general.

                SHUTDOWN IMMEDIATE;
                STARTUP MOUNT;
                ALTER SYSTEM ENABLE RESTRICTED SESSION;
                ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
                ALTER SYSTEM SET AQ_TM_PROCESSES=0;
                ALTER DATABASE OPEN;
                ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
                ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252; --10g
                ALTER DATABASE CHARACTER SET INTERNAL_USE AR8ISO8859P6;--r12 with arabic support
                SHUTDOWN;
                STARTUP RESTRICT;
                SHUTDOWN;
                STARTUP; 
                Pl provide an official doc link to the ALTER DATABASE CHARACTER SET commands for 11gR2 - I do not find them in the 11gR2 manual - http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1004.htm#SQLRF00802

                The commands you are using are unsupported and undocumented for a reason - Re: change NATIONAL CHARACTER SET

                If these are empty databases, why cannot you recreate them with the needed characterset ?
                Sreeni, now I have one question:
                We are using Oracle R12 (db 10.2.0.3) and with arabic support the character set is AR8ISO8859P6. Hundreds of reports were developed using Reports 6i and we are planning to migrate to 11g as a part of our application upgrade process.
                I lead the internal development team and we cannot migrate all existing reports to 10g reports immediately, due to multiple reasons. Hence all possibilities to get the developer 6i reports must be tried

                Now, the question is, I already changed the 11g database character set to AR8ISO8859P6, and developer 6i reports/forms are getting connected to the database and working as expected, without breaking up at point. Please let me know something, as we are migrating from a database which is already on AR8ISO8859P6 to a newer database with AR8ISO8859P6 could cause problems as mentioned with your post?

                regards,

                raj
                If you insist on using undocumented/unsupported commands, then you are on your own :-)

                HTH
                Srini
                • 5. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                  rthampi
                  Thats one of the things I was just thinking about, why I shouldn't create a database with the character set? :) I will try it and get back to the thread Srini. Thank you very much for the lead

                  regards,

                  raj
                  • 6. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                    Sergiusz Wolicki-Oracle
                    Raj,

                    You were unable to use Developer 6i with AL32UTF8, because Developer 6i is based on the Oracle Client 8.0.6 and this client does not understand AL32UTF8. With Developer 6i, you can use the deprecated but still supported character set UTF8, if you want Unicode support.

                    As Srini correctly pointed out, the ALTER DATABASE CHARACTER SET command is not supported with 10.1 or later RDBMS releases. You can use the CSALTER script, as documented in the Database Globalization Support Guide and various support (MOS) notes. Note that this script has limited use, if the database already contains non-ASCII data. Also, the INTERNAL_USE clause is obviously for Oracle's internal use. Customers are allowed to specify it only under supervision and on request of Oracle Support (or myself ;-)).

                    Do you run Developer 6i on Windows? If yes, then the recommended Arabic character set would be AR8MSWIN1256, not AR8ISO8859P6. If you run Developer 6i on Windows, make sure that the NLS_LANG setting is AR8MSWIN1256, not AR8ISO8859P6. If you use AR8ISO8859P6 on Windows , then you may be using a pass-through configuration. Such configuration will cause problems once you migrate to Developer 10g or later. Note that if you run in pass-through configuration (i.e. NLS_LANG = DB character set = AR8ISO8859P6 with Developer on Windows), you cannot just change NLS_LANG to AR8MSWIN1256 if Arabic data is already in the database. More complex repair steps are needed.

                    -- Sergiusz
                    • 7. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                      rthampi
                      S. Wolicki, Oracle wrote:
                      Raj,

                      You were unable to use Developer 6i with AL32UTF8, because Developer 6i is based on the Oracle Client 8.0.6 and this client does not understand AL32UTF8. With Developer 6i, you can use the deprecated but still supported character set UTF8, if you want Unicode support.

                      As Srini correctly pointed out, the ALTER DATABASE CHARACTER SET command is not supported with 10.1 or later RDBMS releases. You can use the CSALTER script, as documented in the Database Globalization Support Guide and various support (MOS) notes. Note that this script has limited use, if the database already contains non-ASCII data. Also, the INTERNAL_USE clause is obviously for Oracle's internal use. Customers are allowed to specify it only under supervision and on request of Oracle Support (or myself ;-)).

                      Do you run Developer 6i on Windows? If yes, then the recommended Arabic character set would be AR8MSWIN1256, not AR8ISO8859P6. If you run Developer 6i on Windows, make sure that the NLS_LANG setting is AR8MSWIN1256, not AR8ISO8859P6. If you use AR8ISO8859P6 on Windows , then you may be using a pass-through configuration. Such configuration will cause problems once you migrate to Developer 10g or later. Note that if you run in pass-through configuration (i.e. NLS_LANG = DB character set = AR8ISO8859P6 with Developer on Windows), you cannot just change NLS_LANG to AR8MSWIN1256 if Arabic data is already in the database. More complex repair steps are needed.

                      -- Sergiusz
                      Thanks Sergi & Srini

                      I dropped the database and created a new database with AR8MSWIN1256 on Windows 8 (Development machine). Actually our ERP R12 instances are running on Linux 64Bit with AR8ISO8859P6 character set, which was the reason I chose to go for the same while installing 11g r2 on the development machine.

                      For certain Arabic reading flexibilities, I had the regional settings parameters with windows set as "Arabic", which must be the reason while a default installation of the database was done, it chose AL32UTF8 as default character set and I had issues with developer 6i environment failing to establish connection.

                      Srini, a special thanks goes to you, as you have pointed out the critical issues with using commands which were outdated.

                      Sergi :P, I sign first the 50,000$ worth of invoices every year from Oracle, lol, We cannot afford to pay one more "Oracle person"

                      Regards,

                      raj
                      • 8. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                        Srini Chavali-Oracle
                        Pl be aware that Win 8 in not currently a certified OS version for database installs

                        HTH
                        Srini
                        • 9. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                          Sergiusz Wolicki-Oracle
                          ERP R12 does not use Developer 6i but rather 10g or 11g (I do not remember which one). Why do you use the old 6i? 6i was needed with ERP R11.5

                          If you run ERP R12 on Linux, then you run in web architecture and the server is on Linux. Then, AR8ISO8859P6 may make more sense due to potential font issues. Please, consult ERP documentation for best advice. Note, you can also run with UTF8 (Dev6i) or AL32UTF8 (Dev10/11g) to be able to implement multilingual support.

                          I did not mean that you should pay anything more to any Oracle person. Both Oracle Support and myself would allow you to use INTERNAL_USE if only if it made sense for and benefited your particular situation. This has nothing to do with hiring us for any extra consulting job :-)


                          -- Sergiusz
                          • 10. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                            Srini Chavali-Oracle
                            EBS R12 uses Forms 10.1.2, not 6i (which is used by EBS 11i)

                            Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12 [ID 437878.1]

                            HTH
                            Srini
                            • 11. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                              rthampi
                              S. Wolicki, Oracle wrote:
                              ERP R12 does not use Developer 6i but rather 10g or 11g (I do not remember which one). Why do you use the old 6i? 6i was needed with ERP R11.5

                              If you run ERP R12 on Linux, then you run in web architecture and the server is on Linux. Then, AR8ISO8859P6 may make more sense due to potential font issues. Please, consult ERP documentation for best advice. Note, you can also run with UTF8 (Dev6i) or AL32UTF8 (Dev10/11g) to be able to implement multilingual support.

                              I did not mean that you should pay anything more to any Oracle person. Both Oracle Support and myself would allow you to use INTERNAL_USE if only if it made sense for and benefited your particular situation. This has nothing to do with hiring us for any extra consulting job :-)


                              -- Sergiusz
                              Hello Sergi/Srini

                              R12 supports reports built by both developer 6i and developer 10g. Obviously the forms are developed using 10g only!. We have retained developers from years, who feel more comfortable with 6i reports and, as well supported hardly make them use 10g reports.
                              10g reports interface switches the color scheme on Windows 7 (Obviously once again not certified?) which make many our team members feel uneasy to use the product. To conclude, I opted to drop the 11g database and create a new one with AR8MSWIN1256 on the develop machine (As I mentioned earlier, this is only for testing and local development requirements) and looks like the "things" are in place :)

                              Thanks once again guys, you were great help and I must say, you guys put some missing sense INTO me :)

                              regards,

                              raj
                              • 12. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                                Srini Chavali-Oracle
                                >
                                R12 supports reports built by both developer 6i and developer 10g. Obviously the forms are developed using 10g only!
                                ...
                                >

                                If you are using Developer 10g , they are all 10g reports - they cannot be version 6i. And R12 supports only 10g, not 6i.

                                HTH
                                Srini
                                • 13. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                                  rthampi
                                  Srini Chavali wrote:
                                  >
                                  R12 supports reports built by both developer 6i and developer 10g. Obviously the forms are developed using 10g only!
                                  ...
                                  >

                                  If you are using Developer 10g , they are all 10g reports - they cannot be version 6i. And R12 supports only 10g, not 6i.

                                  HTH
                                  Srini
                                  R12 supports reports developed by both Developer 6i and Developer 10g and we are using reports developed by both 6i & 10g, to conclude :)

                                  Regards,

                                  raj
                                  • 14. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
                                    Srini Chavali-Oracle
                                    rthampi wrote:
                                    Srini Chavali wrote:
                                    >
                                    R12 supports reports built by both developer 6i and developer 10g. Obviously the forms are developed using 10g only!
                                    ...
                                    >

                                    If you are using Developer 10g , they are all 10g reports - they cannot be version 6i. And R12 supports only 10g, not 6i.

                                    HTH
                                    Srini
                                    R12 supports reports developed by both Developer 6i and Developer 10g and we are using reports developed by both 6i & 10g, to conclude :)

                                    Regards,

                                    raj
                                    Then you are using it in an unsupported manner :-)

                                    Srini
                                    1 2 Previous Next