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 23 Aug 2007 10:02 > If your client NLS_LANG is US7ASCII AND your database NLS_LANG is > US7ASCII, no conversion will take place. THIS will 'corrupt' your > data. > Ben <bal...(a)comcast.net> wrote: > > If you database is setup as us7ascii then no 8 bit data can be stored > > in the first place. correct? If the client was setup as us7ascii also, > > then I don't see the problem. Client can't read or write 8 bit data > > and database can't store 8 bit data, there wouldn't be any need for a > > conversion. What am I missing here? > > Integrity. > > The problem will be that the database has no idea about the encoding you > use for text data. > > The problem will manifest as soon as you try to retrieve these data in > some other context - with a client on some other platform, with > a client that uses a different character set (say, JDBC). > > Yours, > Laurenz Albe I'm not saying it is feasible to have a database set to use US7ASCII as its character set. I'm simply saying that in the scenario that Sybrand listed, 1 database and 1 client both being set to us7ascii, I don't see the issue. UNLESS you introduce a client using a different character set.
From: sybrandb on 23 Aug 2007 14:48 On Thu, 23 Aug 2007 07:02:10 -0700, Ben <balvey(a)comcast.net> wrote: >I'm not saying it is feasible to have a database set to use US7ASCII >as its character set. I'm simply saying that in the scenario that >Sybrand listed, 1 database and 1 client both being set to us7ascii, I >don't see the issue. UNLESS you introduce a client using a different >character set. Ok, again Client set to US7ASCII Database set to US7ASCII You send an eight bit character. Oracle sees 7 bit client character set, 7 bit server character set ---> HEY, I DON'T HAVE TO CONVERT ANY CHARACTER. What will happen if all of a sudden someone decides to export using 7 bit NLS_LANG and import into 8 bit database. Please don't imply I'm making up fairy tales, I'm talking stories for grown ups!!!! REAL WORLD HORROR STORIES with customers getting GROSS!!! And yes: this explanation is on Metalink!!! --
From: sybrandb on 23 Aug 2007 14:53 On 23 Aug 2007 10:21:56 GMT, Laurenz Albe <invite(a)spam.to.invalid> wrote: >2) If you (as misguided admins too often do) mistakenly set NLS_LANG > to the database character set, no character conversion AND NO > INTEGRITY CHECKS are performed and you can store all sort of garbage > in your database without even noticing. It will cause problems later on, > though. This is an Oracle bug in my opinion, although Oracle will > probably disagree with me on this. It's not a bug. Setting the database characterset to the character set of the client is, especially when the server O/S doesn't support it. Setting the client characterset to the characterset of the database, especially when the O/S doesn't support the database characterset is a bug too. Eventually it is a Mickeysoft bug as Mickeysoft is not supporting the correct ISO characterset. It is NOT an Oracle bug! -- Sybrand Bakker Senior Oracle DBA
From: Ben on 23 Aug 2007 15:19 On Aug 23, 2:48 pm, sybra...(a)hccnet.nl wrote: > On Thu, 23 Aug 2007 07:02:10 -0700, Ben <bal...(a)comcast.net> wrote: > >I'm not saying it is feasible to have a database set to use US7ASCII > >as its character set. I'm simply saying that in the scenario that > >Sybrand listed, 1 database and 1 client both being set to us7ascii, I > >don't see the issue. UNLESS you introduce a client using a different > >character set. > > Ok, again > > Client set to US7ASCII > Database set to US7ASCII > You send an eight bit character. > Oracle sees 7 bit client character set, 7 bit server character set > ---> > HEY, I DON'T HAVE TO CONVERT ANY CHARACTER. > What will happen if all of a sudden someone decides to export using 7 > bit NLS_LANG and import into 8 bit database. > > Please don't imply I'm making up fairy tales, I'm talking stories for > grown ups!!!! > REAL WORLD HORROR STORIES with customers getting GROSS!!! > > And yes: this explanation is on Metalink!!! > > -- I'm not implying anything. I'm trying to understand. How do you insert an 8 bit character with a 7 bit client into a db with a 7 bit character set? Wouldn't that be a square peg round hole kind of thing? You of course wouldn't get the 8 bit character back out of the 7 bit db. > >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? The example in Mr Kyte's book is what I am referring to in my original question of not being able to avoid corruption. How can you keep someone from setting their NLS_LANG to us7ascii and updating an 8 bit or multibyte character field? Anytime that happens you would get a replacement character wouldn't you?
From: sybrandb on 23 Aug 2007 18:17
On Thu, 23 Aug 2007 12:19:00 -0700, Ben <balvey(a)comcast.net> wrote: >On Aug 23, 2:48 pm, sybra...(a)hccnet.nl wrote: >> On Thu, 23 Aug 2007 07:02:10 -0700, Ben <bal...(a)comcast.net> wrote: >> >I'm not saying it is feasible to have a database set to use US7ASCII >> >as its character set. I'm simply saying that in the scenario that >> >Sybrand listed, 1 database and 1 client both being set to us7ascii, I >> >don't see the issue. UNLESS you introduce a client using a different >> >character set. >> >> Ok, again >> >> Client set to US7ASCII >> Database set to US7ASCII >> You send an eight bit character. >> Oracle sees 7 bit client character set, 7 bit server character set >> ---> >> HEY, I DON'T HAVE TO CONVERT ANY CHARACTER. >> What will happen if all of a sudden someone decides to export using 7 >> bit NLS_LANG and import into 8 bit database. >> >> Please don't imply I'm making up fairy tales, I'm talking stories for >> grown ups!!!! >> REAL WORLD HORROR STORIES with customers getting GROSS!!! >> >> And yes: this explanation is on Metalink!!! >> >> -- > >I'm not implying anything. I'm trying to understand. > >How do you insert an 8 bit character with a 7 bit client into a db >with a 7 bit character set? Wouldn't that be a square peg round hole >kind of thing? You of course wouldn't get the 8 bit character back out >of the 7 bit db. Yes, you would. You didn't read what I wrote. 7 bit client, 7 bit database characterset. NO CONVERSION!!!! What part of 'NO CONVERSION' you don't understand???? > >> >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? > >The example in Mr Kyte's book is what I am referring to in my original >question of not being able to avoid corruption. How can you keep >someone from setting their NLS_LANG to us7ascii and updating an 8 bit >or multibyte character field? Anytime that happens you would get a >replacement character wouldn't you? > There would be CONVERSION!!!!!!! Whether that conversion would be succesful is a a different story. And in modern organisations end-users are not allowed to edit the registry or to set environment variables. -- Sybrand Bakker Senior Oracle DBA |