FAQ
I am migrating a website from Oracle 8i to 11g, and one of the snags
I've run into is the character set.

The old database has both NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET
set to US7ASCII. This character set seems to have disappeared in
11g. I tried making a database using Oracle's defaults, which are
WE8MSWIN1252 and AL16UTF16 respectively. The data looks ok so far,
but my client is worried about problems we just haven't seen yet.

My question: are there other choices for the two settings which would
give us a better chance of having unmangled data than the ones Oracle
chose? If it matters, he's worried about the diacritics specifically,
though I don't think he has actually found any yet that look wrong.

Also, a couple of related questions:

In 8i, I set the NLS_DATE_FORMAT by adding a line to the init.ora
file. In my Googling I have seen suggestions to set this by writing
out a pfile, editing that, then converting it back to an spfile,
basically doing the same thing I used to do but with the added steps
of translating from and to the binary format. This seems clunky;
isn't there a better way?

Lastly, the client noticed that NLS_TIME_TZ_FORMAT has changed, not to
his liking. Would I set this in the same way as NLS_DATE_FORMAT,
whatever that turns out to be?

Thanks for being patient with some very newbie questions!

janine

Search Discussions

  • Janine Sisk at Jan 7, 2010 at 9:17 pm
    No, sorry I should have specified - all servers run Linux (CentOS). I
    believe the client's development systems are running Windows, but that
    doesn't matter so much. I did think it was odd, now that you mention
    it, that Oracle recommends a Windows character set on Linux, but that
    is what it's doing.

    janine
    On Jan 7, 2010, at 1:13 PM, Paul Drake wrote:

    All clients and app servers running MS Windows?
    On Thu, Jan 7, 2010 at 4:10 PM, Janine Sisk wrote:
    I am migrating a website from Oracle 8i to 11g, and one of the
    snags I've
    run into is the character set.

    The old database has both NLS_CHARACTERSET and
    NLS_NCHAR_CHARACTERSET set to
    US7ASCII. This character set seems to have disappeared in 11g. I
    tried
    making a database using Oracle's defaults, which are WE8MSWIN1252 and
    AL16UTF16 respectively. The data looks ok so far, but my client is
    worried
    about problems we just haven't seen yet.

    My question: are there other choices for the two settings which
    would give
    us a better chance of having unmangled data than the ones Oracle
    chose? If
    it matters, he's worried about the diacritics specifically, though
    I don't
    think he has actually found any yet that look wrong.

    Also, a couple of related questions:

    In 8i, I set the NLS_DATE_FORMAT by adding a line to the init.ora
    file. In
    my Googling I have seen suggestions to set this by writing out a
    pfile,
    editing that, then converting it back to an spfile, basically doing
    the same
    thing I used to do but with the added steps of translating from and
    to the
    binary format. This seems clunky; isn't there a better way?

    Lastly, the client noticed that NLS_TIME_TZ_FORMAT has changed, not
    to his
    liking. Would I set this in the same way as NLS_DATE_FORMAT,
    whatever that
    turns out to be?

    Thanks for being patient with some very newbie questions!

    janine

    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.completestreets.org/faq.html
    http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf
    ---
    Janine Sisk
    President/CEO of furfly, LLC
    503-693-6407
  • Jared Still at Jan 7, 2010 at 10:00 pm

    On Thu, Jan 7, 2010 at 1:10 PM, Janine Sisk wrote:
    The old database has both NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET set
    to US7ASCII. This character set seems to have disappeared in 11g. I tried
    making a database using Oracle's defaults, which are WE8MSWIN1252 and
    AL16UTF16 respectively. The data looks ok so far, but my client is worried
    about problems we just haven't seen yet.
    I am not an expert on NLS, but have had to deal with it on occasion.

    The US7ASCII set is a subset of the WE8MSWIN1252
    You could check the docs on that:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/applocaledata.htm#g681575

    Problems could be encountered if the client set NLS_LANG to some value
    other than US7ASCII and encoded ASCII values higher than 127 into the data.

    Or something like that - I've never had to deal with it myself, but I have
    seen
    others discuss problems along that line.

    If that has not occurred, it should not be a problem.

    You will probably want to read the chapter on Character Set Migration:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#g1011430

    Also, a couple of related questions:
    In 8i, I set the NLS_DATE_FORMAT by adding a line to the init.ora file. In
    my Googling I have seen suggestions to set this by writing out a pfile,
    editing that, then converting it back to an spfile, basically doing the same
    thing I used to do but with the added steps of translating from and to the
    binary format. This seems clunky; isn't there a better way?
    alter system set nls_date_format = scope=spfile should do
    it.
    Lastly, the client noticed that NLS_TIME_TZ_FORMAT has changed, not to his
    liking. Would I set this in the same way as NLS_DATE_FORMAT, whatever that
    turns out to be?
    Yes:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams152.htm#CHDHGECF
    <http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams152.htm#CHDHGECF>

    Both parameter require restarting the instance.

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com
  • David Mann at Jan 7, 2010 at 11:07 pm
    When I had clients worried about character set conversions I usually ran
    Character Set Scanner utility CSSCAN on a copy of the database to check for
    differences. Here is the reference in the 10g documentation, assuming it is
    still available in 11g but don't have a link handy.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml

    --
    http://www.freelists.org/webpage/oracle-l
  • Janine Sisk at Jan 11, 2010 at 7:37 pm
    Thanks to everyone who replied....

    I ran CSSCAN on the original 8.1.7 database and, of course, ran into
    trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is
    lossy in a number of places. This does not surprise me terribly;
    Jared mentioned that you can put "invalid" data into a database of
    type US7ASCII and I'm pretty sure that all of the programmers who have
    worked on this site over the years have just assumed that if the
    database didn't choke on it, then it was ok.

    What concerns me is that CSSCAN reports that converting to UTF8 will
    have the exact same lossy conversions. The two error files are
    literally identical except for the value of TOCHAR. I thought that
    UTF8 was the mother of all character sets, so where do I go from here?

    As an example, one of the first errors reported looks like this when I
    do a SELECT in sqlplus:

    Lic. en medios de comunicaci&Atilde;?3n

    I will be digging further into this, with The Google and all, but if
    anyone has any light to shed, please do!

    janine
    On Jan 7, 2010, at 3:07 PM, David Mann wrote:

    When I had clients worried about character set conversions I usually
    ran Character Set Scanner utility CSSCAN on a copy of the database
    to check for differences. Here is the reference in the 10g
    documentation, assuming it is still available in 11g but don't have
    a link handy.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Jan 11, 2010 at 7:49 pm
    Hi Janine,

    Problem is that "LOSSY" may mean that the data is already corrupted in the source database, so, Oracle has no idea what to convert it to. In the case of US7ASCII, character values range from 0-127, correct? So, if you have any values 128 or greater, Oracle has no idea what character that value should map to. It would depend on what *assumption* the client code was operating under when the data was inserted. So, it doesn't matter if AL32UTF8 is the "mother of all character sets". Even if AL32UTF8 *does* have the character you need, it doesn't matter, cause Oracle doesn't know what that character is in the source. If it can't determine that, it can't map it to the correct character in the destination character set.

    See Doc ID 444701.1 "CSSCAN Output Explained", for more info, particularly "B.4) LOSSY data".

    Hope that helps,

    -Mark

    From: oracle-l-bounce_at_freelists.org On Behalf Of Janine Sisk
    Sent: Monday, January 11, 2010 2:37 PM
    To: oracle-l L
    Subject: Re: Replacement of US7ASCII character set in 11g?

    Thanks to everyone who replied....

    I ran CSSCAN on the original 8.1.7 database and, of course, ran into trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is lossy in a number of places. This does not surprise me terribly; Jared mentioned that you can put "invalid" data into a database of type US7ASCII and I'm pretty sure that all of the programmers who have worked on this site over the years have just assumed that if the database didn't choke on it, then it was ok.

    What concerns me is that CSSCAN reports that converting to UTF8 will have the exact same lossy conversions. The two error files are literally identical except for the value of TOCHAR. I thought that UTF8 was the mother of all character sets, so where do I go from here?

    As an example, one of the first errors reported looks like this when I do a SELECT in sqlplus:

    Lic. en medios de comunicaci&Atilde;?3n

    I will be digging further into this, with The Google and all, but if anyone has any light to shed, please do!

    janine

    On Jan 7, 2010, at 3:07 PM, David Mann wrote:

    When I had clients worried about character set conversions I usually ran Character Set Scanner utility CSSCAN on a copy of the database to check for differences. Here is the reference in the 10g documentation, assuming it is still available in 11g but don't have a link handy.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm
  • Janine Sisk at Jan 11, 2010 at 10:14 pm
    Mark, thanks for that! You gave me the idea to run CSSCAN and tell it
    to convert the data from WE8MSWIN1252 to UTF8. Voila, no more
    errors. At least now I know what Oracle thought it was doing when
    this data was inserted.

    So (I think) what I need to do is get the data out of 8.1.7, but have
    Oracle treat it as WE8MSWIN1252 as-is, without converting it. If it
    tries to convert it, I'll probably lose all those 128 and above
    characters.

    I am going to try experimenting with this, but if anyone has any
    educated guesses I'd love to hear them.

    Also, sadly I no longer have Metalink/MOS access; I am doing this as
    an outside contractor to one tiny piece of a huge organization, and I
    am not allowed to use their CSI. I used to have my own, but over the
    years my smaller clients have all converted to Postgres; our database
    usage is fairly minimal and doesn't justify the licensing expense.
    So, no more Metalink for me. :(

    janine
    On Jan 11, 2010, at 11:49 AM, Bobak, Mark wrote:

    Hi Janine,

    Problem is that �LOSSY� may mean that the data is already corrupted
    in the source database, so, Oracle has no idea what to convert it
    to. In the case of US7ASCII, character values range from 0-127,
    correct? So, if you have any values 128 or greater, Oracle has no
    idea what character that value should map to. It would depend on
    what *assumption* the client code was operating under when the data
    was inserted. So, it doesn�t matter if AL32UTF8 is the �mother of
    all character sets�. Even if AL32UTF8 *does* have the character you
    need, it doesn�t matter, cause Oracle doesn�t know what that
    character is in the source. If it can�t determine that, it can�t
    map it to the correct character in the destination character set.

    See Doc ID 444701.1 �CSSCAN Output Explained�, for more info,
    particularly �B.4) LOSSY data�.

    Hope that helps,

    -Mark

    From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce@freelists.org
    ] On Behalf Of Janine Sisk
    Sent: Monday, January 11, 2010 2:37 PM
    To: oracle-l L
    Subject: Re: Replacement of US7ASCII character set in 11g?

    Thanks to everyone who replied....

    I ran CSSCAN on the original 8.1.7 database and, of course, ran into
    trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is
    lossy in a number of places. This does not surprise me terribly;
    Jared mentioned that you can put "invalid" data into a database of
    type US7ASCII and I'm pretty sure that all of the programmers who
    have worked on this site over the years have just assumed that if
    the database didn't choke on it, then it was ok.

    What concerns me is that CSSCAN reports that converting to UTF8 will
    have the exact same lossy conversions. The two error files are
    literally identical except for the value of TOCHAR. I thought that
    UTF8 was the mother of all character sets, so where do I go from here?

    As an example, one of the first errors reported looks like this when
    I do a SELECT in sqlplus:

    Lic. en medios de comunicaci&Atilde;?3n

    I will be digging further into this, with The Google and all, but if
    anyone has any light to shed, please do!

    janine

    On Jan 7, 2010, at 3:07 PM, David Mann wrote:


    When I had clients worried about character set conversions I usually
    ran Character Set Scanner utility CSSCAN on a copy of the database
    to check for differences. Here is the reference in the 10g
    documentation, assuming it is still available in 11g but don't have
    a link handy.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
    ---
    Janine Sisk
    President/CEO of furfly, LLC
    503-693-6407
  • Robert Freeman at Jan 11, 2010 at 10:32 pm
    Also, sadly I no longer have Metalink/MOS access; I am doing this as
    an outside contractor to one tiny >> piece of a huge organization, and I
    am not allowed to use their CSI.
    All I can say is that is an organization that would not get my consulting services! :) I believe strongly in having the right tools for the job, or not doing the job at all. That's just me... :)

    RF

    Robert G. Freeman
    Oracle ACE
    Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
    Author:
    Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON!
    OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
    Oracle Database 11g New Features (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Other various titles
    Blog: http://robertgfreeman.blogspot.com
    Check out my new blog series on installing Oracle Database 11gR2 on Windows using VMWare!

    ________________________________
    From: Janine Sisk
    To: "Bobak, Mark"
    Cc: oracle-l L
    Sent: Mon, January 11, 2010 3:14:49 PM
    Subject: Re: Replacement of US7ASCII character set in 11g?

    Mark, thanks for that! You gave me the idea to run CSSCAN and tell it to convert the data from WE8MSWIN1252 to UTF8. Voila, no more errors. At least now I know what Oracle thought it was doing when this data was inserted.

    So (I think) what I need to do is get the data out of 8.1.7, but have Oracle treat it as WE8MSWIN1252 as-is, without converting it. If it tries to convert it, I'll probably lose all those 128 and above characters.

    I am going to try experimenting with this, but if anyone has any educated guesses I'd love to hear them.

    Also, sadly I no longer have Metalink/MOS access; I am doing this as an outside contractor to one tiny piece of a huge organization, and I am not allowed to use their CSI. I used to have my own, but over the years my smaller clients have all converted to Postgres; our database usage is fairly minimal and doesn't justify the licensing expense. So, no more Metalink for me. :(

    janine

    On Jan 11, 2010, at 11:49 AM, Bobak, Mark wrote:

    Hi Janine,
    Problem is that “LOSSY” may mean that the data is already corrupted in the source database, so, Oracle has no idea what to convert it to. In the case of US7ASCII, character values range from 0-127, correct? So, if you have any values 128 or greater, Oracle has no idea what character that value should map to. It would depend on what *assumption* the client code was operating under when the data was inserted. So, it doesn’t matter if AL32UTF8 is the “mother of all character sets”. Even if AL32UTF8 *does* have the character you need, it doesn’t matter, cause Oracle doesn’t know what that character is in the source. If it can’t determine that, it can’t map it to the correct character in the destination character set.

    See Doc ID 444701.1 “CSSCAN Output Explained”, for more info, particularly “B.4) LOSSY data”.

    Hope that helps,

    -Mark

    From: oracle-l-bounce_at_freelists.org On Behalf Of Janine Sisk
    Sent: Monday, January 11, 2010 2:37 PM
    To: oracle-l L
    Subject: Re: Replacement of US7ASCII character set in 11g?

    Thanks to everyone who replied....

    I ran CSSCAN on the original 8.1.7 database and, of course, ran into trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is lossy in a number of places. This does not surprise me terribly; Jared mentioned that you can put "invalid" data into a database of type US7ASCII and I'm pretty sure that all of the programmers who have worked on this site over the years have just assumed that if the database didn't choke on it, then it was ok.

    What concerns me is that CSSCAN reports that converting to UTF8 will have the exact same lossy conversions. The two error files are literally identical except for the value of TOCHAR. I thought that UTF8 was the mother of all character sets, so where do I go from here?

    As an example, one of the first errors reported looks like this when I do a SELECT in sqlplus:

    Lic. en medios de comunicaci&Atilde;?3n

    I will be digging further into this, with The Google and all, but if anyone has any light to shed, please do!

    janine

    On Jan 7, 2010, at 3:07 PM, David Mann wrote:



    When I had clients worried about character set conversions I usually ran Character Set Scanner utility CSSCAN on a copy of the database to check for differences. Here is the reference in the 10g documentation, assuming it is still available in 11g but don't have a link handy.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
    ---
    Janine Sisk
    President/CEO of furfly, LLC
    503-693-6407
  • Amit bansal at Jan 14, 2010 at 2:12 pm
    Janine,

    You can run csscan with FROMCHAR and TOCHAR set to WE8MSWIN1252. If this
    does not report any lossy data i.e only changeless data, then you can change
    the database characterset to WE8MSWIN1252 first. You need to use following
    command

    alter database characterset;
    In 10g and above this is replaced by csalter which ensures that you have run
    csscan and there is no convertible/lossy data.

    Then again run csscan with tochar=al32utf8 to confirm if there is no
    convertible or lossy data. I think win*1252 is not strict superset of
    AL32UTF8, so there might be some characters whose codepoint might change
    and can be reported as Convertible. In this case you will have to export
    /import the data.

    Cheers
    Amit
    http://askdba.org/weblog/
    On Tue, Jan 12, 2010 at 6:14 AM, Janine Sisk wrote:

    Mark, thanks for that! You gave me the idea to run CSSCAN and tell it to
    convert the data from WE8MSWIN1252 to UTF8. Voila, no more errors. At
    least now I know what Oracle thought it was doing when this data was
    inserted.

    So (I think) what I need to do is get the data out of 8.1.7, but have
    Oracle treat it as WE8MSWIN1252 as-is, without converting it. If it tries
    to convert it, I'll probably lose all those 128 and above characters.

    I am going to try experimenting with this, but if anyone has any educated
    guesses I'd love to hear them.

    Also, sadly I no longer have Metalink/MOS access; I am doing this as an
    outside contractor to one tiny piece of a huge organization, and I am not
    allowed to use their CSI. I used to have my own, but over the years my
    smaller clients have all converted to Postgres; our database usage is
    fairly minimal and doesn't justify the licensing expense. So, no more
    Metalink for me. :(

    janine

    On Jan 11, 2010, at 11:49 AM, Bobak, Mark wrote:

    Hi Janine,

    Problem is that “LOSSY” may mean that the data is already corrupted in the
    source database, so, Oracle has no idea what to convert it to. In the case
    of US7ASCII, character values range from 0-127, correct? So, if you have
    any values 128 or greater, Oracle has no idea what character that value
    should map to. It would depend on what **assumption** the client code was
    operating under when the data was inserted. So, it doesn’t matter if
    AL32UTF8 is the “mother of all character sets”. Even if AL32UTF8 **does**
    have the character you need, it doesn’t matter, cause Oracle doesn’t know
    what that character is in the source. If it can’t determine that, it can’t
    map it to the correct character in the destination character set.

    See Doc ID 444701.1 “CSSCAN Output Explained”, for more info, particularly
    “B.4) LOSSY data”.

    Hope that helps,

    -Mark

    *From:* oracle-l-bounce_at_freelists.org [
    mailto:oracle-l-bounce_at_freelists.org ] *On
    Behalf Of *Janine Sisk
    *Sent:* Monday, January 11, 2010 2:37 PM
    *To:* oracle-l L
    *Subject:* Re: Replacement of US7ASCII character set in 11g?

    Thanks to everyone who replied....

    I ran CSSCAN on the original 8.1.7 database and, of course, ran into
    trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is lossy
    in a number of places. This does not surprise me terribly; Jared mentioned
    that you can put "invalid" data into a database of type US7ASCII and I'm
    pretty sure that all of the programmers who have worked on this site over
    the years have just assumed that if the database didn't choke on it, then it
    was ok.

    What concerns me is that CSSCAN reports that converting to UTF8 will have
    the exact same lossy conversions. The two error files are literally
    identical except for the value of TOCHAR. I thought that UTF8 was the
    mother of all character sets, so where do I go from here?

    As an example, one of the first errors reported looks like this when I do a
    SELECT in sqlplus:

    Lic. en medios de comunicaci&Atilde;?3n

    I will be digging further into this, with The Google and all, but if anyone
    has any light to shed, please do!

    janine

    On Jan 7, 2010, at 3:07 PM, David Mann wrote:


    When I had clients worried about character set conversions I usually ran
    Character Set Scanner utility CSSCAN on a copy of the database to check for
    differences. Here is the reference in the 10g documentation, assuming it is
    still available in 11g but don't have a link handy.


    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml



    ---
    Janine Sisk
    President/CEO of furfly, LLC
    503-693-6407



    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Jan 11, 2010 at 7:51 pm
    Janine,


    I regret to say that I have seen that before. Your database is
    US7ASCII but the client that inserted the data was WE8ISO8859P1 or some
    such. The ? is Oracle doing the best it can with the data. Regrettably
    without knowing what was suppose to have been entered you'll never
    figure it out without dumping the numeric code that it is. I believe
    you might be able to do that with the ASCIISTR function.


    Dick Goulet
    Senior Oracle DBA/NA Team Lead
    PAREXEL International



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Janine Sisk
    Sent: Monday, January 11, 2010 2:37 PM
    To: oracle-l L
    Subject: Re: Replacement of US7ASCII character set in 11g?

    Thanks to everyone who replied....

    I ran CSSCAN on the original 8.1.7 database and, of course, ran into
    trouble right away. The conversion from US7ASCII to WE8MSWIN1252 is
    lossy in a number of places. This does not surprise me terribly; Jared
    mentioned that you can put "invalid" data into a database of type
    US7ASCII and I'm pretty sure that all of the programmers who have worked
    on this site over the years have just assumed that if the database
    didn't choke on it, then it was ok.

    What concerns me is that CSSCAN reports that converting to UTF8 will
    have the exact same lossy conversions. The two error files are
    literally identical except for the value of TOCHAR. I thought that UTF8
    was the mother of all character sets, so where do I go from here?

    As an example, one of the first errors reported looks like this when I
    do a SELECT in sqlplus:

    Lic. en medios de comunicaci&Atilde;?3n

    I will be digging further into this, with The Google and all, but if
    anyone has any light to shed, please do!

    janine

    On Jan 7, 2010, at 3:07 PM, David Mann wrote:

    When I had clients worried about character set conversions I
    usually ran Character Set Scanner utility CSSCAN on a copy of the
    database to check for differences. Here is the reference in the 10g
    documentation, assuming it is still available in 11g but don't have a
    link handy.



    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scann
    er.htm


    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml

    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Jan 8, 2010 at 2:03 pm
    Janine,

    I'm a little surprised that your client didn't make the move to
    WE8ISO8859P1 some years ago as US7ASCII kinda became unacceptable in a
    global world. I moved several databases back then to WE8 and there were
    no problems at all. Those have since moved onto the Unicode character
    sets without difficulty either. CSSCAN is a good utility, but in
    general you will be just fine.

    Dick Goulet
    Senior Oracle DBA/NA Team Lead
    PAREXEL International

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Janine Sisk
    Sent: Thursday, January 07, 2010 4:10 PM
    To: oracle-l L
    Subject: Replacement of US7ASCII character set in 11g?

    I am migrating a website from Oracle 8i to 11g, and one of the snags
    I've run into is the character set.

    The old database has both NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET
    set to US7ASCII. This character set seems to have disappeared in
    11g. I tried making a database using Oracle's defaults, which are
    WE8MSWIN1252 and AL16UTF16 respectively. The data looks ok so far,
    but my client is worried about problems we just haven't seen yet.

    My question: are there other choices for the two settings which would
    give us a better chance of having unmangled data than the ones Oracle
    chose? If it matters, he's worried about the diacritics specifically,
    though I don't think he has actually found any yet that look wrong.

    Also, a couple of related questions:

    In 8i, I set the NLS_DATE_FORMAT by adding a line to the init.ora
    file. In my Googling I have seen suggestions to set this by writing
    out a pfile, editing that, then converting it back to an spfile,
    basically doing the same thing I used to do but with the added steps
    of translating from and to the binary format. This seems clunky;
    isn't there a better way?

    Lastly, the client noticed that NLS_TIME_TZ_FORMAT has changed, not to
    his liking. Would I set this in the same way as NLS_DATE_FORMAT,
    whatever that turns out to be?

    Thanks for being patient with some very newbie questions!

    janine

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Yechiel Adar at Jan 14, 2010 at 1:40 pm
    As long as the database charset and the client charset are the same no
    conversion is done.
    So, if you have us7ascii charset and you put in chr(240) it will go as
    240 to the database and will come back as 240.
    If you use WE8MSWIN1252 charset on the database and us7ascii on the
    client, the sql client on the client machine will
    try conversion and will return ? (chr(191) or chr(96) if I remember
    correctly) that say unknown char.

    Try changing the database charset to WE8MSWIN1252 and use the same
    charset on the client, you will probably have no problems.

    Adar Yechiel
    Rechovot, Israel

    Janine Sisk wrote:
    I am migrating a website from Oracle 8i to 11g, and one of the snags
    I've run into is the character set.

    The old database has both NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET
    set to US7ASCII. This character set seems to have disappeared in
    11g. I tried making a database using Oracle's defaults, which are
    WE8MSWIN1252 and AL16UTF16 respectively. The data looks ok so far,
    but my client is worried about problems we just haven't seen yet.

    My question: are there other choices for the two settings which would
    give us a better chance of having unmangled data than the ones Oracle
    chose? If it matters, he's worried about the diacritics specifically,
    though I don't think he has actually found any yet that look wrong.

    Also, a couple of related questions:

    In 8i, I set the NLS_DATE_FORMAT by adding a line to the init.ora
    file. In my Googling I have seen suggestions to set this by writing
    out a pfile, editing that, then converting it back to an spfile,
    basically doing the same thing I used to do but with the added steps
    of translating from and to the binary format. This seems clunky;
    isn't there a better way?

    Lastly, the client noticed that NLS_TIME_TZ_FORMAT has changed, not to
    his liking. Would I set this in the same way as NLS_DATE_FORMAT,
    whatever that turns out to be?

    Thanks for being patient with some very newbie questions!

    janine

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 7, '10 at 9:10p
activeJan 14, '10 at 2:12p
posts12
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase