Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: Encoding issue on 9i

    Hi,

    I am trying to load a few CSV files into Oracle using the sqlldr - The files are encoded using ISO-8859-1 - The database character set is also set to Western European ISO-8859-1, However, once imported some of the characters appear as inverted question marks - the characters are the plus minus character and the super script 2 - Both these character are supported by the ISO-8859-1 - Any idea why this is happening and what's the workaround? - Also the same imported on a Windows machine using UTF-8 as the characterset works fine

    Regards

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    Set the NLS_LANG environment variable to correct encoding.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2007
    Posts
    5

    Encoding Issue

    Hi,

    I have the NLS_LANG set to AMERICAN_AMERICA.WE8ISO8859P1 - Should it be set to something else??

    Regards

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    It also has to be set when you run the utility to LOOK at the data which may have loaded OK.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Oct 2007
    Posts
    5

    Encoding

    If you are referring to the .ctl file then yes i have tried that too - I ran a dump to determine what the database is actually storing and it seemed that it was storing ASCII codes - meaning that the loader must be interpreting the datafile as being ASCII characters - I added the characterset argument to the Load Data statement as US7ASCII hoping that the Loader would convert the codes to ISO 8859-1 (since the database characterset is set to that) - but had no luck

    Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would be MUCH more helpful & useful if you simply used CUT & PASTE (along with <code tags>) to actually show us exactly what you are doing & the complete output response.

    We don't know Operating System name or version.
    We don't know Oracle version (to 4 decimal places).

    See what you are telling us everything should be OK & working.
    The fact you are having a problem means, IMO, means you are mis-stating reality, but since we can't see what you are really doing & what you are actually seeing; we can't really provide any solution.

    >I added the characterset argument to the Load Data statement as US7ASCII
    Part of me suspects this is not what should have been done.
    In 7-bit ASCII there is NO support any character value greater than 127; CHR(127)
    >- the characters are the plus minus character and the super script 2 - Both these character are supported by the ISO-8859-1
    but neither are supported in 7-bit ASCII (aka US7ASCII)

    Please read & FOLLOW posting guidelines found below:
    http://www.dbforums.com/showthread.php?t=1031644
    Last edited by anacedent; 10-13-07 at 23:03.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2007
    Posts
    5

    Encoding Issue

    This is the environment information:

    Oracle9i Enterprise Edition Release 9.2.0.4.0 on a Linux kernel version 2.6 (Fedora Core 3)

    And this is the NLS_Lang and database character settings:

    SQL> HOST echo $NLS_LANG;
    AMERICAN_AMERICA.WE8ISO8859P1

    SQL> SELECT * from NLS_DATABASE_PARAMETERS;

    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8ISO8859P1
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM

    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    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 9.2.0.4.0

    20 rows selected.

    Table Information:

    SQL> desc productdescriptions;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    PRODUCTID NOT NULL NUMBER
    DESCRIPTION VARCHAR2(500)
    ISDEFAULT NOT NULL NUMBER(1)
    TYPE NOT NULL NUMBER
    LOCALEID NOT NULL NUMBER


    What i am trying to do is load a CSV file in the above table using the SQL loader - the CSV file contains the character which once loaded shows up as

    Thanks..

  8. #8
    Join Date
    Oct 2007
    Posts
    5

    Encoding Issue

    Hi,

    I have figured out the issue - It was due to the fact that the NLS_LANG was not set correctly - Setting it correctly before the run did the trick - thanks for all the help

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I find metalink.oracle.com to be extremely helpful.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I find metalink.oracle.com to be extremely helpful.
    Please refrain form posting this truism against every thread just because it would be true.

    I find the manuals located at http://tahiti.oracle.com to be extremely helpful.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •