what characterset to use?
in [Oracle]
|
Prev: OID/LDAP problem - Any help with a fix would be appreciated
Next: Oracle11 & statistics calculations
From: Ben on 22 Aug 2007 08:55 10.2.0.2 Ent Ed, AIX5L We are planning a new database and I'm having a little confusion on what character set to use. We are not sure if in the future we may need some double byte characters. For the most part we can suffice with AMERICAN_AMERICA.WE8MSWIN1252 but it is possible that in the future we need multibyte characters. I have been reading the Globalization guide and the section in Tom Kyte's Expert Oracle Database Architecture on this but I could be totally mis interrepting something here. I haven't read chapter 6 on unicode in the Globalization Guide (yet). I don't want to use multibyte data unless it is necessary but I do want to have the option available. 1. Am I correct in thinking that if we use NLS_CHARACTERSET = WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET = AL16UTF16 we shouldn't have any issues with losing multibyte characters? 2. On my windows client I go to a command prompt and issue the following c:\> echo %NLS_LANG%, I get %NLS_LANG% returned back to me. I'm assuming that this means I don't have the variable set. How does my client then treat the data that I am getting from the database? 3. When I query v$nls_parameters does this show me what character set my client session is using? 4. When I query nls_database_parameters, does this show me what the database has been setup as on the server side? 5. Within sqlplus what does 'show parameter nls' tell me? I'm assuming the init.ora nls settings.
From: Ben on 22 Aug 2007 09:08 > 1. Am I correct in thinking that if we use NLS_CHARACTERSET = > WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET = AL16UTF16 we shouldn't have > any issues with losing multibyte characters? I need to clarify, we shouldn't have any issues storing multibyte data, as long as we use NVARCHAR data types for the data. > 2. On my windows client I go to a command prompt and issue the > following c:\> echo %NLS_LANG%, > I get %NLS_LANG% returned back to me. I'm assuming that this means I > don't have the variable set. How does my client then treat the data > that I am getting from the database? By 'treat' I mean what character set does the client use.
From: Ben on 22 Aug 2007 11:39 One other thought. How could it be possible to guarantee that you would never corrupt your data in a WE8MSWIN1252 database character set or any other multibyte character set? You don't really have control over what character set all the clients connect with, do you? If you have a client that uses US7ASCII and they select then update based on results, you could potentially corrupt all your data. no?
From: sybrandb on 22 Aug 2007 14:52 On Wed, 22 Aug 2007 05:55:54 -0700, Ben <balvey(a)comcast.net> wrote: >10.2.0.2 Ent Ed, AIX5L > >We are planning a new database and I'm having a little confusion on >what character set to use. We are not sure if in the future we may >need some double byte characters. For the most part we can suffice >with AMERICAN_AMERICA.WE8MSWIN1252 but it is possible that in the >future we need multibyte characters. I have been reading the >Globalization guide and the section in Tom Kyte's Expert Oracle >Database Architecture on this but I could be totally mis interrepting >something here. I haven't read chapter 6 on unicode in the >Globalization Guide (yet). I don't want to use multibyte data unless >it is necessary but I do want to have the option available. > >1. Am I correct in thinking that if we use NLS_CHARACTERSET = >WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET = AL16UTF16 we shouldn't have >any issues with losing multibyte characters? > The basic guideline is you use a characterset which is native to the database server. As your database is on AIX5L, this is WE8ISO8859P15, NOT mswin1252. Oracle however is moving towards UTF, and I believe I did read a note ALL 11g databases are going to be UTF by default. >2. On my windows client I go to a command prompt and issue the >following c:\> echo %NLS_LANG%, >I get %NLS_LANG% returned back to me. I'm assuming that this means I >don't have the variable set. How does my client then treat the data >that I am getting from the database? The Oracle default for all O/S es is still US7ASCII. However, if you did install your client properly, you will have a *registry* string NLS_LANG set to MSWIN1252. This will work. > >3. When I query v$nls_parameters does this show me what character set >my client session is using? It should but better query v$nls_session_parameters. V$ views have documented descriptions BTW: in the database (use DIC) in the documentation, and on Metalink. > >4. When I query nls_database_parameters, does this show me what the >database has been setup as on the server side? > see answer 3 >5. Within sqlplus what does 'show parameter nls' tell me? I'm assuming >the init.ora nls settings. Exactly, you 'assume'. Your assumption is incorrect. It will show you the result from v$parameter. If you started your database using a spfile (which you should do anyway), the parameters from the spfile will show. -- Sybrand Bakker Senior Oracle DBA
From: sybrandb on 22 Aug 2007 14:54
On Wed, 22 Aug 2007 06:08:34 -0700, Ben <balvey(a)comcast.net> wrote: >> 1. Am I correct in thinking that if we use NLS_CHARACTERSET = >> WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET = AL16UTF16 we shouldn't have >> any issues with losing multibyte characters? > >I need to clarify, we shouldn't have any issues storing multibyte >data, as long as we use NVARCHAR data types for the data. > If you don't want issues you should use a multibyte characterset as *database* characterset. > >> 2. On my windows client I go to a command prompt and issue the >> following c:\> echo %NLS_LANG%, >> I get %NLS_LANG% returned back to me. I'm assuming that this means I >> don't have the variable set. How does my client then treat the data >> that I am getting from the database? > >By 'treat' I mean what character set does the client use. > > > Responded to this one earlier. -- Sybrand Bakker Senior Oracle DBA |