This discussion is archived
13 Replies Latest reply: Nov 4, 2013 3:42 PM by Sergiusz Wolicki (Oracle) RSS

display data as ???? after converting the character set

916384 Newbie
Currently Being Moderated

I am using Oracle10g release 10.2.0.1.0 on Windows environment. I have changed the character set of my database to AR8MSWIN1256 (for Farsi font) but after running the below commands the data change to ???? character. My system regional setting set Farsi as default language.


The steps which i performed is


SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

STARTUP;


Kindly guide.

  • 1. Re: display data as ???? after converting the character set
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    this is not the method to change the Database character set you have to user the CSSCAN utility to change this

    seems you used the Doc :Character Set Migration but  you should read  very first line "The ALTER DATABASE CHARACTER SET statement does not perform any data conversion,"

     

    You can use the method  from here :ORACLE-BASE - Character Set Migration using CSSCAN and CSALTER

     

    HTH

  • 2. Re: display data as ???? after converting the character set
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    The steps you have used are no longer applicable to database versions 10gR1 and above. They have mostly likely corrupted your database beyond repair. You will have to restore from a good backup, then follow the methods in the doc - Character Set Migration

  • 3. Re: display data as ???? after converting the character set
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated

    I assume your database was US7ASCII before the change.

     

    While the steps you performed are, indeed, not supported with 10g, they should not have corrupted your database, if it was US7ASCII.  To get your characters right, you need to make sure that you use a client that is capable of displaying Arabic and, if required, you set the NLS_LANG variable to the character set of this client.

     

    What application exactly and on what platform do you use to test your Arabic input/output?

     

     

    Thanks,

    Sergiusz

  • 4. Re: display data as ???? after converting the character set
    916384 Newbie
    Currently Being Moderated

    I have performed the steps of oracle documents

     

    To change the database character set, perform the following steps:

    1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
    2. Do a full backup of the database, because the CSALTER script cannot be rolled back.
    3. Start up the database.
    4. Run the Database Character Set Scanner utility.
      CSSCAN /AS SYSDBA FULL=Y...

    5. Run the CSALTER script.
      @@CSALTER.PLB
      SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL;
      STARTUP;

    CSALTER also run successfully and shows the message "CSALTER operation completed, please restart database"

     

    I have also change the registry variable NLS_LANG=AMERICAN_AMERICA.AR8MSWIN1256 and set default language to FARSI and restart the system but still data shows as ??????


    Result of

    select * from v$nls_parameters;

     

    PARAMETERVALUE
    NLS_LANGUAGEAMERICAN
    NLS_TERRITORYAMERICA
    NLS_CURRENCY$
    NLS_ISO_CURRENCYAMERICA
    NLS_NUMERIC_CHARACTERS.,
    NLS_CALENDARGREGORIAN
    NLS_DATE_FORMATDD-MON-RR
    NLS_DATE_LANGUAGEAMERICAN
    NLS_CHARACTERSETAR8MSWIN1256
    NLS_SORTBINARY
    NLS_TIME_FORMATHH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY$
    NLS_NCHAR_CHARACTERSETAL16UTF16
    NLS_COMPBINARY
    NLS_LENGTH_SEMANTICSBYTE
    NLS_NCHAR_CONV_EXCPFALSE
  • 5. Re: display data as ???? after converting the character set
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl post the output of the CSSCAN process. What client or tool are you using to view the data ? Can you reproduce the issue if you use SQL Developer to view the data ?

     

    HTH
    Srini 

  • 6. Re: display data as ???? after converting the character set
    916384 Newbie
    Currently Being Moderated

    Output of CSSCAN is

     

    Microsoft Windows XP [Version 5.1.2600]

    (C) Copyright 1985-2001 Microsoft Corp.

     

    C:\Documents and Settings\Fahed akh>csscan

     

     

    Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Sun Oct 20 05:40

    :16 2013

     

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

     

     

    Username: examdari/examd@dari

     

    Connected to:

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

    With the Partitioning, OLAP and Data Mining options

     

    (1)Full database, (2)User, (3)Table, (4)Column: 1 > 2

     

    Current database character set is AR8MSWIN1256.

     

    Enter new database character set name: > AR8MSWIN1256

     

    Enter array fetch buffer size: 1024000 >

     

    Enter number of scan processes to utilize(1..32): 1 >

     

    Enter user name to scan: > EXAMDARI

     

    Enumerating tables to scan...

     

    . process 1 scanning EXAMDARI.STUDENT_PICTURE[AAAMkLAAEAAAErJAAA]

    . process 1 scanning EXAMDARI.STUDENTS_EXAMS[AAAMkKAAEAAAEpJAAA]

    . process 1 scanning EXAMDARI.STUDENTS_CLASS_ENROLMENT[AAAMkGAABAAAOspAAA]

    . process 1 scanning EXAMDARI.STUDENTS_ENROLMENT[AAAMkHAABAAAOtBAAA]

    . process 1 scanning EXAMDARI.EXAM_TYPES[AAAMj9AABAAAOsRAAA]

    . process 1 scanning EXAMDARI.LOGIN[AAAMkCAAEAAAEohAAA]

    . process 1 scanning EXAMDARI.SPECILIZATIONS[AAAMkEAAEAAAEoxAAA]

    . process 1 scanning EXAMDARI.CLASSES[AAAMjzAABAAAOrxAAA]

    . process 1 scanning EXAMDARI.AA[AAAMjyAAEAAAElZAAA]

    . process 1 scanning EXAMDARI.GRADE_GPA[AAAMkBAAEAAAEoZAAA]

    . process 1 scanning EXAMDARI.CLASS_MAIN[AAAMj3AAEAAAElhAAA]

    . process 1 scanning EXAMDARI.DEPT[AAAMj7AAEAAAEmBAAA]

    . process 1 scanning EXAMDARI.CLASS_CODES[AAAMj1AABAAAOsBAAA]

    . process 1 scanning EXAMDARI.SUBJECTS[AAAMkMAAEAAAExJAAA]

     

    Creating Database Scan Summary Report...

     

    Creating Individual Exception Report...

     

    Scanner terminated successfully.

     

     

    For viewing output I am using TOAD & SQLPLUS

  • 7. Re: display data as ???? after converting the character set
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Your CSSCAN was run for only one user, not the entire database. Pl see if you can reproduce the issue by viewing the data using SQL Developer.

     

    HTH
    Srini

  • 8. Re: display data as ???? after converting the character set
    916384 Newbie
    Currently Being Moderated

    I have run the CSSCAN for whole database also but result is same.Same issue when viewing data form SQL DEVELOPER,but when i create new table having one column (varchar2) and insert some farsi data on it then it display perfectly for new data but show ???? for old data.

     

    CREATE TABLE test(c1 varchar2(100))

     

    insert into test values('گگسشیشسیشسسشیشسی')

     

    select * from test

    output=گگسشيشسيشسسشيشسي

  • 9. Re: display data as ???? after converting the character set
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl confirm that you recovered the database from a good backup before you ran the CSSCAN and CSALTER process.

     

    HTH
    Srini

  • 10. Re: display data as ???? after converting the character set
    916384 Newbie
    Currently Being Moderated

    Yes i have recovered the database from good backup.

  • 11. Re: display data as ???? after converting the character set
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated

    Your CSSCAN output says "Current database character set is AR8MSWIN1256.".  If this output is from scanning a database recovered from backup then why does it say "AR8MSWIN1256"?

     

    Regarding the old data that displays incorrectly, it may be corrupted in the database. This corruption could happen at the time of inserting and have nothing to do with the database character set change. Select any value in any row of any table that displays incorrectly and run the following query to check the internal binary representation of the value:

     

      SELECT DUMP(<column>,1016) FROM <table> WHERE <row condition>

     

    Where <column> is the column name you selected, <table> is the name of the table containing this column and <row condition> is a condition that selects the given row you selected (e.g. ROWID=... or <primary key>=...). Post the result to this thread.

     

     

    Thanks,

    Sergiusz

  • 12. Re: display data as ???? after converting the character set
    916384 Newbie
    Currently Being Moderated

    Your CSSCAN output says "Current database character set is AR8MSWIN1256.".  If this output is from scanning a database recovered from backup then why does it say "AR8MSWIN1256"?


    This is because i have used the old steps to convert the character set,when i came to know that these steps are no more valid for 10g then i apply new steps.

     

    Result of query is

     

    Query : SELECT DUMP(class_name,1016) FROM classes WHERE class_id = 1

     

    Result : Typ=1 Len=9 CharacterSet=AR8MSWIN1256: bf,bf,bf,bf,bf,20,bf,bf,bf

  • 13. Re: display data as ???? after converting the character set
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated

    The sequence of bytes (0xbf, 0xbf, 0xbf, 0xbf, 0xbf, 0x20, 0xbf, 0xbf, 0xbf) shows that your data is corrupted in the database. Code 0xbf corresponds to the default replacement character in WE8ISO8859P1 and WE8MSWIN1252 character sets. My best guess is that your database was previously in WE8ISO8859P1 or WE8MSWIN1252 character set and this data was inserted from a properly configured client, such as SQL Developer. The data could not be represented in the WE8... database character set, so it was converted to the default replacement character. This incorrect data cannot be repaired just by correcting the database character set declaration. It is too late. You need to fix the character set (already done, as I see) and reenter the incorrect data from a properly configured client.

     

     

    Thanks,

    Sergiusz

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points