Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    19

    Unanswered: oracle 8i export to oracle 12c chinese character issue

    Hi, All

    I will be very appreciate if someone can give me some hints. I have been testing several days to find out the issue of display Chinese character problem.


    Export server Oracle version (Unix machine)

    Oracle8i Enterprise Edition Release 8.1.7.0.0

    select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
    'NLS_LANGUAGE', 'LANGUAGE',
    'NLS_TERRITORY', 'TERRITORY') name,
    value from v$nls_parameters
    WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')

    Name Value
    language american
    territory american
    character US7ASCII


    set nls_lang=american_america.us7ascii in my machine

    I can display traditional chinese character in sqlplus properly



    exp userAdm/pwd01@sampleDB FULL=Y FILE=sampleDB.dmp

    export successfully
    ---------------------------------------------



    Import server Oracle version
    Oracle Database 12c release 12.1.0.1.0 64bit

    I create a new db on the import server (Windows 7)

    Name Value
    language american
    territory american
    character ZNT16MSWIN95


    set nls_lang=american_america.us7ascii before import

    imp userAdm/pwd01@sampleDB file=sampleDB.dmp log=sampleDB.log full=y

    import successfully

    However, when I set nls_lang=american_america.us7ascii in my machine

    I CANNOT display traditional chinese character in sqlplus properly

    Also tried following language, still in vain

    set nls_lang=TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
    set nls_lang=AMERICAN_AMERICA.AL16UTF16
    set nls_lang=american_america.ZHT16MSWIN950

    Can anyone give me some hints?
    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can display traditional chinese character in sqlplus properly

    please humor me and modify the SELECT below to succeed running against the V8.1.7 DB

    SELECT * FROM V$VERSION;
    SELECT CHINESE_COL, ASCIISTR(CHINESE_COL) FROM CHINESE_TBL WHERE ROWNUM = 1;

    use COPY & PASTE to post back here the SQL & the results
    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.

  3. #3
    Join Date
    May 2011
    Posts
    19
    Hi anacedent,

    Thank you for your reply, below is the results.

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    PL/SQL Release 8.1.7.0.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for Solaris: Version 8.1.7.0.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production


    SQL> select office_type, office_code, telephone_no, chinese_name, ASCIISTR(chinese_name) from office order by office_code;
    select office_type, office_code, telephone_no, chinese_name, ASCIISTR(chinese_name) from office order by office_code
    *
    ERROR at line 1:
    ORA-00904: invalid column name



    The ASCIISTR function can be used in the following versions of Oracle/PLSQL:

    •Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
    Last edited by manhjk; 12-14-14 at 22:08.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from SQL below

    select chinese_name, length(chinese_name) from office where rownum < 21;
    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.

  5. #5
    Join Date
    May 2011
    Posts
    19
    before set nls_lang=american_america.us7ascii on client machine

    SQL> select chinese_name, length(chinese_name) from office where rownum < 21;

    CHINESE_NAME LENGTH(CHINESE_NAME)
    -------------------------------------------------- --------------------
    2D$T'x 6
    2D%|'x 6
    2D$Q$-'x 8
    2D$Q$K'x 8
    2D$G$Q$-'x 10
    2D$G$Q$K'x 10
    2D$G'x 6
    2D$-'x 6
    2D$;'x 6
    2D$Ch 6
    2D$E'x 6

    CHINESE_NAME LENGTH(CHINESE_NAME)
    -------------------------------------------------- --------------------
    2D$Q'x 6
    2D$Q$G'x 8
    2D$Q$T'x 8
    2D$Q$C'x 8
    2D$Q$E'x 8
    2D$G$Q'x 8
    2D$G$Q$G'x 10
    2D$G$Q$T'x 10
    2D$G$Q%|'x 10

    20 rows selected.

  6. #6
    Join Date
    May 2011
    Posts
    19
    After set nls_lang=american_america.us7ascii on client machine

    SQL> select chinese_name, length(chinese_name) from office where rownum < 21;

    CHINESE_NAME LENGTH(CHINESE_NAME)
    -------------------------------------------------- --------------------
    第三村 6
    第四村 6
    第十五村 8
    第十八村 8
    第二十五村 10
    第二十八村 10
    第二村 6
    第五村 6
    第六村 6
    第七 6
    第九村 6

    CHINESE_NAME LENGTH(CHINESE_NAME)
    -------------------------------------------------- --------------------
    第十村 6
    第十二村 8
    第十三村 8
    第十七村 8
    第十九村 8
    第二十村 8
    第二十二村 10
    第二十三村 10
    第二十四村 10

    20 rows selected.

Posting Permissions

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