+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Oracle 10G - character sets.

  1. Oracle 10G - character sets.

    Good day,

    I hope someone will be able to help me out with an issue related to
    character sets and Oracle 10g.

    I have no experience with Oracle - but was asked to extract data out of
    2 .dmp files by means of setting up a new oracle instance, using IMP to
    import them and whatever method was best to extract the imported data
    into text format.

    I installed Oracle, used IMP and all worked smoothly.
    However, I can't extract the data from the imported tables.

    Spooling the rows into a text file won't work for the tables contain
    blobs. I built this PHP 'client' that connects to oracle (via OCI),
    retrieves the rows and stores them into text files, however that
    doesn't work because no matter what character set I would use ( via
    NLS_LANG ) I would get either '?' or 'omegas' presumable because the
    charset conversion failed. I decided to build a new client ( C++, using
    the oracle instantclient sdk ) but also ran into the same problem with
    charsets. (Using OCIEnvNlsCreate() to explicitly specify the charset
    won't help, either )

    The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    doesn't with my clients ( PHP and C ). Using different NLS_LANG
    combinations seem to make a difference, yet I am still getting charset
    conversion issues.

    Both sqlplus and my clients use the same library (libclntsh.so.10.1),
    yet sqlplus must be doing something that I miss - something that allows
    it to display the characters properly.

    Thank you in advance for any ideas or tips,
    Mark


  2. Re: Oracle 10G - character sets.

    markpapadakis wrote:
    > I built this PHP 'client' that connects to oracle (via OCI),
    > retrieves the rows and stores them into text files, however that
    > doesn't work because no matter what character set I would use ( via
    > NLS_LANG ) I would get either '?' or 'omegas' presumable because the
    > charset conversion failed.


    '?' means that there is a conversion problem.

    What exactly do you mean by 'omegas'?
    Do you mean the greek character with UNICODE code point 0x03A9 or 0x03C9
    or are you referring to the five-character string 'omega'?

    > The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    > GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    > doesn't with my clients ( PHP and C ). Using different NLS_LANG
    > combinations seem to make a difference, yet I am still getting charset
    > conversion issues.


    There is no EL8ISO8859P, (Greek would be EL8ISO8859P7).
    Maybe your problem is a typo really.

    On the other hand, if you are trying to store greek characters in a
    database, WE8MSWIN1252 is the wrong database character set for you
    because there are no greek letters in this codepage (the exception being
    "mu", used as "micro" sign, which is UNICODE 0x00B5).

    Can you be more detailed on the following points:

    - What is your operating system and Oracle version?
    - What is the system character set when you run
    a) SQL*Plus
    b) PHP
    - What is your database character set?
    - What was the database character set of the database from which the
    export files originate?
    - What characters cause problems for you?
    - What is the exact setting of NLS_LANG when
    a) things look right with SQL*Plus
    b) things look wrong with SQL*Plus
    c) things look wrong with PHP
    - What exactly do you get instead of the problem characters when it is
    NOT a question mark?

    Yours,
    Laurenz Albe

  3. Re: Oracle 10G - character sets.

    markpapadakis wrote:
    > I built this PHP 'client' that connects to oracle (via OCI),
    > retrieves the rows and stores them into text files, however that
    > doesn't work because no matter what character set I would use ( via
    > NLS_LANG ) I would get either '?' or 'omegas' presumable because the
    > charset conversion failed.


    '?' means that there is a conversion problem.

    What exactly do you mean by 'omegas'?
    Do you mean the greek character with UNICODE code point 0x03A9 or 0x03C9
    or are you referring to the five-character string 'omega'?

    > The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    > GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    > doesn't with my clients ( PHP and C ). Using different NLS_LANG
    > combinations seem to make a difference, yet I am still getting charset
    > conversion issues.


    There is no EL8ISO8859P, (Greek would be EL8ISO8859P7).
    Maybe your problem is a typo really.

    On the other hand, if you are trying to store greek characters in a
    database, WE8MSWIN1252 is the wrong database character set for you
    because there are no greek letters in this codepage (the exception being
    "mu", used as "micro" sign, which is UNICODE 0x00B5).

    Can you be more detailed on the following points:

    - What is your operating system and Oracle version?
    - What is the system character set when you run
    a) SQL*Plus
    b) PHP
    - What is your database character set?
    - What was the database character set of the database from which the
    export files originate?
    - What characters cause problems for you?
    - What is the exact setting of NLS_LANG when
    a) things look right with SQL*Plus
    b) things look wrong with SQL*Plus
    c) things look wrong with PHP
    - What exactly do you get instead of the problem characters when it is
    NOT a question mark?

    Yours,
    Laurenz Albe

  4. Re: Oracle 10G - character sets.


    Thank you for replying, Laurenz

    >
    > What exactly do you mean by 'omegas'?
    > Do you mean the greek character with UNICODE code point 0x03A9 or 0x03C9
    > or are you referring to the five-character string 'omega'?


    Omegas are 'Ώ'. That is, not the five character string.

    >
    > > The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    > > GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    > > doesn't with my clients ( PHP and C ). Using different NLS_LANG
    > > combinations seem to make a difference, yet I am still getting charset
    > > conversion issues.

    >
    > There is no EL8ISO8859P, (Greek would be EL8ISO8859P7).
    > Maybe your problem is a typo really.
    >


    The typo occurred when I was typing the message - I meant to say
    'EL8ISO8859P7'. I am sorry about that.

    > On the other hand, if you are trying to store greek characters in a
    > database, WE8MSWIN1252 is the wrong database character set for you
    > because there are no greek letters in this codepage (the exception being
    > "mu", used as "micro" sign, which is UNICODE 0x00B5).
    >


    nls_database_parameters.NLS_CHARACTERSET is 'WE8MSWIN1252'.
    If I set NLS_LANG properly (i.e export
    NLS_LANG=GREEK_GREECE.EL8ISO8859P7 ) and run sqlplus, retrieving the
    rows (i.e SELECT columnNames from TABLE ) works just fine - I get the
    data back in greek.

    Both clients seem to acknowledge the fact that NLS_LANG is defined, for
    I get different output depending on the value of the NLS_LANG
    env.variable, however, instead of Greek characters I get either '?' or
    the 'omega' character I mentioned above. It is as if sqlplus is
    performing an additional step when it retrieves the rows - a step I may
    be missing.

    > Can you be more detailed on the following points:
    >
    > - What is your operating system and Oracle version?

    Redhat Linux, ES 3
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    > - What is the system character set when you run
    > a) SQL*Plus

    GREEK_GREECE.EL8ISO8859P7

    > b) PHP

    GREEK_GREECE.EL8ISO8859P7

    > - What is your database character set?

    By that, I assume you mean nls_database_parameters.NLS_CHARACTERSET,
    which is == "WE8MSWIN1252"

    > - What was the database character set of the database from which the
    > export files originate?


    According to import:

    import done in US7ASCII character set and AL16UTF16 NCHAR character set
    import server uses WE8MSWIN1252 character set (possible charset
    conversion)
    export client uses EL8ISO8859P7 character set (possible charset
    conversion)



    > - What characters cause problems for you?

    All greek characters for both PHP and C++ client. Work perfectly with
    sqlplus

    > - What is the exact setting of NLS_LANG when
    > a) things look right with SQL*Plus

    GREEK_GREECE.EL8ISO8859P7 ( or any other territory and language
    combination, as long as the charset portion is == "EL8ISO8859P7 " )

    > b) things look wrong with SQL*Plus


    Any other charset value other than "EL8ISO8859P7"


    > c) things look wrong with PHP

    GREEK_GREECE.EL8ISO8859P7 or basically any other possible value.

    > - What exactly do you get instead of the problem characters when it is
    > NOT a question mark?
    >


    When I use LANG_TERRITORY.UTF8 ( or specify it explicitly using
    OCIEnvNlsCreate() ), I get something like:
    ΒΏeΒΏΒΏsΒΏΒΏΒΏΒΏΒΏΒΏ stΒΏΒΏ ΒΏΒΏΒΏΒΏpΒΏ


    Thank you very much,
    Mark

    > Yours,
    > Laurenz Albe



  5. Re: Oracle 10G - character sets.


    Thank you for replying, Laurenz

    >
    > What exactly do you mean by 'omegas'?
    > Do you mean the greek character with UNICODE code point 0x03A9 or 0x03C9
    > or are you referring to the five-character string 'omega'?


    Omegas are 'Ώ'. That is, not the five character string.

    >
    > > The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    > > GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    > > doesn't with my clients ( PHP and C ). Using different NLS_LANG
    > > combinations seem to make a difference, yet I am still getting charset
    > > conversion issues.

    >
    > There is no EL8ISO8859P, (Greek would be EL8ISO8859P7).
    > Maybe your problem is a typo really.
    >


    The typo occurred when I was typing the message - I meant to say
    'EL8ISO8859P7'. I am sorry about that.

    > On the other hand, if you are trying to store greek characters in a
    > database, WE8MSWIN1252 is the wrong database character set for you
    > because there are no greek letters in this codepage (the exception being
    > "mu", used as "micro" sign, which is UNICODE 0x00B5).
    >


    nls_database_parameters.NLS_CHARACTERSET is 'WE8MSWIN1252'.
    If I set NLS_LANG properly (i.e export
    NLS_LANG=GREEK_GREECE.EL8ISO8859P7 ) and run sqlplus, retrieving the
    rows (i.e SELECT columnNames from TABLE ) works just fine - I get the
    data back in greek.

    Both clients seem to acknowledge the fact that NLS_LANG is defined, for
    I get different output depending on the value of the NLS_LANG
    env.variable, however, instead of Greek characters I get either '?' or
    the 'omega' character I mentioned above. It is as if sqlplus is
    performing an additional step when it retrieves the rows - a step I may
    be missing.

    > Can you be more detailed on the following points:
    >
    > - What is your operating system and Oracle version?

    Redhat Linux, ES 3
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    > - What is the system character set when you run
    > a) SQL*Plus

    GREEK_GREECE.EL8ISO8859P7

    > b) PHP

    GREEK_GREECE.EL8ISO8859P7

    > - What is your database character set?

    By that, I assume you mean nls_database_parameters.NLS_CHARACTERSET,
    which is == "WE8MSWIN1252"

    > - What was the database character set of the database from which the
    > export files originate?


    According to import:

    import done in US7ASCII character set and AL16UTF16 NCHAR character set
    import server uses WE8MSWIN1252 character set (possible charset
    conversion)
    export client uses EL8ISO8859P7 character set (possible charset
    conversion)



    > - What characters cause problems for you?

    All greek characters for both PHP and C++ client. Work perfectly with
    sqlplus

    > - What is the exact setting of NLS_LANG when
    > a) things look right with SQL*Plus

    GREEK_GREECE.EL8ISO8859P7 ( or any other territory and language
    combination, as long as the charset portion is == "EL8ISO8859P7 " )

    > b) things look wrong with SQL*Plus


    Any other charset value other than "EL8ISO8859P7"


    > c) things look wrong with PHP

    GREEK_GREECE.EL8ISO8859P7 or basically any other possible value.

    > - What exactly do you get instead of the problem characters when it is
    > NOT a question mark?
    >


    When I use LANG_TERRITORY.UTF8 ( or specify it explicitly using
    OCIEnvNlsCreate() ), I get something like:
    ΒΏeΒΏΒΏsΒΏΒΏΒΏΒΏΒΏΒΏ stΒΏΒΏ ΒΏΒΏΒΏΒΏpΒΏ


    Thank you very much,
    Mark

    > Yours,
    > Laurenz Albe



  6. Re: Oracle 10G - character sets.



    On Jan 8, 12:25*pm, "markpapadakis" wrote:
    > Thank you for replying, Laurenz
    >
    >
    >
    > > What exactly do you mean by 'omegas'?
    > > Do you mean the greek character with UNICODE code point 0x03A9 or 0x03C9
    > > or are you referring to the five-character string 'omega'?Omegas are 'Ώ'. That is, not the five character string.

    >
    >
    >
    > > > The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    > > > GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    > > > doesn't with my clients ( PHP and C ). Using different NLS_LANG
    > > > combinations seem to make a difference, yet I am still getting charset
    > > > conversion issues.

    >
    > > There is no EL8ISO8859P, (Greek would be EL8ISO8859P7).
    > > Maybe your problem is a typo really.The typo occurred when I was typingthe message - I meant to say

    > 'EL8ISO8859P7'. I am sorry about that.
    >
    > > On the other hand, if you are trying to store greek characters in a
    > > database, WE8MSWIN1252 is the wrong database character set for you
    > > because there are no greek letters in this codepage (the exception being
    > > "mu", used as "micro" sign, which is UNICODE 0x00B5).nls_database_parameters.NLS_CHARACTERSET is 'WE8MSWIN1252'.

    > If I set NLS_LANG properly (i.e export
    > NLS_LANG=GREEK_GREECE.EL8ISO8859P7 ) and run sqlplus, retrieving the
    > rows (i.e SELECT columnNames from TABLE ) works just fine - I get the
    > data back in greek.
    >
    > Both clients seem to acknowledge the fact that NLS_LANG is defined, for
    > I get different output depending on the value of the NLS_LANG
    > env.variable, however, instead of Greek characters I get either '?' or
    > the 'omega' character I mentioned above. It is as if sqlplus is
    > performing an additional step when it retrieves the rows - a step I may
    > be missing.
    >
    > > Can you be more detailed on the following points:

    >
    > > - What is your operating system and Oracle version?Redhat Linux, ES 3

    > Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    >
    > > - What is the system character set when you run
    > > * a) SQL*PlusGREEK_GREECE.EL8ISO8859P7

    >
    > > * b) PHPGREEK_GREECE.EL8ISO8859P7

    >
    > > - What is your database character set?By that, I assume you mean nls_database_parameters.NLS_CHARACTERSET,

    > which is == "WE8MSWIN1252"
    >
    > > - What was the database character set of the database from which the
    > > * export files originate?According to import:

    >
    > import done in US7ASCII character set and AL16UTF16 NCHAR character set
    > import server uses WE8MSWIN1252 character set (possible charset
    > conversion)
    > export client uses EL8ISO8859P7 character set (possible charset
    > conversion)
    >
    > > - What characters cause problems for you?All greek characters for both PHP and C++ client. Work perfectly with

    > sqlplus
    >
    > > - What is the exact setting of NLS_LANG when
    > > * a) things look right with SQL*PlusGREEK_GREECE.EL8ISO8859P7 ( orany other territory and language

    > combination, as long as the charset portion is == "EL8ISO8859P7 " )
    >
    > > * b) things look wrong with SQL*PlusAny other charset value other than "EL8ISO8859P7"

    >
    > > * c) things look wrong with PHPGREEK_GREECE.EL8ISO8859P7 *or basically any other possible value.

    >
    > > - What exactly do you get instead of the problem characters when it is
    > > * NOT a question mark?When I use LANG_TERRITORY.UTF8 ( or specify it explicitly using

    > OCIEnvNlsCreate() ), I get something like:
    > ΒΏeΒΏΒΏsΒΏΒΏΒΏΒΏΒΏΒΏ stΒΏΒΏ ΒΏΒΏΒΏΒΏpΒΏ
    >
    > Thank you very much,
    > Mark
    >
    >
    >
    > > Yours,
    > > Laurenz Albe- Hide quoted text -- Show quoted text -



    You have it all here

    According to import:

    import done in US7ASCII character set and AL16UTF16 NCHAR character set

    import server uses WE8MSWIN1252 character set (possible charset
    conversion)
    export client uses EL8ISO8859P7 character set (possible charset
    conversion)


    NLS_LANG was .US7ASCII in your client session during import.
    Hence your problem.

    --
    Sybrand Bakker
    Senior Oracle DBA


  7. Re: Oracle 10G - character sets.



    On Jan 8, 12:25*pm, "markpapadakis" wrote:
    > Thank you for replying, Laurenz
    >
    >
    >
    > > What exactly do you mean by 'omegas'?
    > > Do you mean the greek character with UNICODE code point 0x03A9 or 0x03C9
    > > or are you referring to the five-character string 'omega'?Omegas are 'Ώ'. That is, not the five character string.

    >
    >
    >
    > > > The rows are in WE8MSWIN1252 encoding - setting NLS_LANG to
    > > > GREEK_GREECE.EL8ISO8859P works just fine with sqlplus. However, it
    > > > doesn't with my clients ( PHP and C ). Using different NLS_LANG
    > > > combinations seem to make a difference, yet I am still getting charset
    > > > conversion issues.

    >
    > > There is no EL8ISO8859P, (Greek would be EL8ISO8859P7).
    > > Maybe your problem is a typo really.The typo occurred when I was typingthe message - I meant to say

    > 'EL8ISO8859P7'. I am sorry about that.
    >
    > > On the other hand, if you are trying to store greek characters in a
    > > database, WE8MSWIN1252 is the wrong database character set for you
    > > because there are no greek letters in this codepage (the exception being
    > > "mu", used as "micro" sign, which is UNICODE 0x00B5).nls_database_parameters.NLS_CHARACTERSET is 'WE8MSWIN1252'.

    > If I set NLS_LANG properly (i.e export
    > NLS_LANG=GREEK_GREECE.EL8ISO8859P7 ) and run sqlplus, retrieving the
    > rows (i.e SELECT columnNames from TABLE ) works just fine - I get the
    > data back in greek.
    >
    > Both clients seem to acknowledge the fact that NLS_LANG is defined, for
    > I get different output depending on the value of the NLS_LANG
    > env.variable, however, instead of Greek characters I get either '?' or
    > the 'omega' character I mentioned above. It is as if sqlplus is
    > performing an additional step when it retrieves the rows - a step I may
    > be missing.
    >
    > > Can you be more detailed on the following points:

    >
    > > - What is your operating system and Oracle version?Redhat Linux, ES 3

    > Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    >
    > > - What is the system character set when you run
    > > * a) SQL*PlusGREEK_GREECE.EL8ISO8859P7

    >
    > > * b) PHPGREEK_GREECE.EL8ISO8859P7

    >
    > > - What is your database character set?By that, I assume you mean nls_database_parameters.NLS_CHARACTERSET,

    > which is == "WE8MSWIN1252"
    >
    > > - What was the database character set of the database from which the
    > > * export files originate?According to import:

    >
    > import done in US7ASCII character set and AL16UTF16 NCHAR character set
    > import server uses WE8MSWIN1252 character set (possible charset
    > conversion)
    > export client uses EL8ISO8859P7 character set (possible charset
    > conversion)
    >
    > > - What characters cause problems for you?All greek characters for both PHP and C++ client. Work perfectly with

    > sqlplus
    >
    > > - What is the exact setting of NLS_LANG when
    > > * a) things look right with SQL*PlusGREEK_GREECE.EL8ISO8859P7 ( orany other territory and language

    > combination, as long as the charset portion is == "EL8ISO8859P7 " )
    >
    > > * b) things look wrong with SQL*PlusAny other charset value other than "EL8ISO8859P7"

    >
    > > * c) things look wrong with PHPGREEK_GREECE.EL8ISO8859P7 *or basically any other possible value.

    >
    > > - What exactly do you get instead of the problem characters when it is
    > > * NOT a question mark?When I use LANG_TERRITORY.UTF8 ( or specify it explicitly using

    > OCIEnvNlsCreate() ), I get something like:
    > ΒΏeΒΏΒΏsΒΏΒΏΒΏΒΏΒΏΒΏ stΒΏΒΏ ΒΏΒΏΒΏΒΏpΒΏ
    >
    > Thank you very much,
    > Mark
    >
    >
    >
    > > Yours,
    > > Laurenz Albe- Hide quoted text -- Show quoted text -



    You have it all here

    According to import:

    import done in US7ASCII character set and AL16UTF16 NCHAR character set

    import server uses WE8MSWIN1252 character set (possible charset
    conversion)
    export client uses EL8ISO8859P7 character set (possible charset
    conversion)


    NLS_LANG was .US7ASCII in your client session during import.
    Hence your problem.

    --
    Sybrand Bakker
    Senior Oracle DBA


  8. Re: Oracle 10G - character sets.



    >
    > NLS_LANG was .US7ASCII in your client session during import.
    > Hence your problem.
    >
    > --
    > Sybrand Bakker
    > Senior Oracle DBA


    Thank you Sybrand,

    However, after dropping the user(schema) and trying again, this time
    having set NLS_LANG prior to invoking imp

    import done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character
    set
    import server uses WE8MSWIN1252 character set (possible charset
    conversion)

    still doesn't work. I am getting '?' instead of greek characters. It
    works just fine on sqlplus though.

    Mark


  9. Re: Oracle 10G - character sets.



    >
    > NLS_LANG was .US7ASCII in your client session during import.
    > Hence your problem.
    >
    > --
    > Sybrand Bakker
    > Senior Oracle DBA


    Thank you Sybrand,

    However, after dropping the user(schema) and trying again, this time
    having set NLS_LANG prior to invoking imp

    import done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character
    set
    import server uses WE8MSWIN1252 character set (possible charset
    conversion)

    still doesn't work. I am getting '?' instead of greek characters. It
    works just fine on sqlplus though.

    Mark


  10. Re: Oracle 10G - character sets.



    On Jan 8, 1:43 pm, "markpapadakis" wrote:
    > > NLS_LANG was .US7ASCII in your client session during import.
    > > Hence your problem.

    >
    > > --
    > > Sybrand Bakker
    > > Senior Oracle DBAThank you Sybrand,

    >
    > However, after dropping the user(schema) and trying again, this time
    > having set NLS_LANG prior to invoking imp
    >
    > import done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character
    > set
    > import server uses WE8MSWIN1252 character set (possible charset
    > conversion)
    >
    > still doesn't work. I am getting '?' instead of greek characters. It
    > works just fine on sqlplus though.
    >
    > Mark


    Still doesn't work points to characterset conversion and/or incorrect
    NLS_LANG settings somewhere.

    For your reference

    http://www.oracle.com/technology/tec...plications.pdf

    and
    http://www.oracle.com/technology/tec...lang%20faq.htm

    which I got after searching for 'PHP NLS_LANG' on the Oracle site.

    --
    Sybrand Bakker
    Senior Oracle DBA


+ Reply to Thread
Page 1 of 3 1 2 3 LastLast