This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 17, 2012 4:04 AM by rthampi RSS

Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125

rthampi Explorer
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Read MOS Notes :
    Which Character Set Supports Which Language [ID 62421.1]
  • 3. Re: Difference between NLS_CHARACTERSETAL32UTF8 & NLS_CHARACTERSET WE8MSWIN125
    rthampi Explorer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    >
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points