DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: nls characters

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    Hi,

    We have uploaded data into a table and ut had special characters in it ('éêÃ*áèç'). Yet when I type out the following query I get :
    SQL> l
    1 select id, name
    2* from cat where id = 10044680
    SQL> /

    ID
    ----------
    NAME
    --------------------------------------------
    10044680
    eeaaec

    I also am pasting my db parameters when I type in a sho parameters: (I am only pasting the NLS specific ones):
    nls_calendar string
    nls_comp string
    nls_currency string
    nls_date_format string
    nls_date_language string
    nls_dual_currency string
    nls_iso_currency string
    nls_language string AMERICAN

    NAME TYPE VALUE
    ------------------------------------ ------- ------------------------------
    nls_numeric_characters string
    nls_sort string
    nls_territory string AMERICA
    nls_time_format string
    nls_time_tz_format string
    nls_timestamp_format string
    nls_timestamp_tz_format string


    I am curious to know whether it gets stored correctly and is just displaying wrong..(how can I check this???)

    Also if I can find a way to get new inserts correctly what happens to records already there?

    Thanks,
    Nirasha

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    I'm missing the values of the parameters, what is the nls_characterset of the database and what is the nls_lang parameter of the client?

  3. #3
    Join Date
    Nov 2000
    Posts
    205


    I am curious to know whether it gets stored correctly and is just displaying wrong..(how can I check this???)


    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 US7ASCII
    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 TZH:TZM
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_NCHAR_CHARACTERSET US7ASCII
    NLS_RDBMS_VERSION 8.1.6.1.0

    18 rows selected.

    Also if I can find a way to get new inserts correctly what happens to records already there?


    I am aslo not sure which characterset to use.. I have seen a list of the valid ones but have no idea of the differences...


    I have heard that I might need to change the characterset of the database, and I am trying to understand the impact of this. Also if I just change the session's parameter for the characterset, will I be able to store the values with accents (even though I have a characterset that does not allow these).

    Please tell me I don't have to recreate the db.

    Thanks,
    Nirasha

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    With character set US7ASCII they are stored as 'eeaaec' instead of 'éêÃ*áèç'. Character set WE8ISO8859P1 supports most western European languages. Check Oracle8i National Language Support Guide of the Oracle Documentation, available at OTN (otn.oracle.com).

    In Oracle 7 you had to recreate the database to change the character set, in 8i you can change the character set by ALTER DATABASE db1 CHARACTER SET WE8ISO8859P1;
    Check Oracle8i SQL Reference for the restrictions. And perform a full backup before you try to change the character set.

  5. #5
    Join Date
    Nov 2000
    Posts
    205

    Question

    Thanks so much, that was a great help. I think I am ecstatic at the
    prospect of not having to recreate the database.
    Thanks for your reply. I just have a few more concerns. I have read all of the
    documentation on the Oracle site and my understanding is the following:
    shutdown the database
    take backup
    startup restrictive mode
    alter database ....
    shutdown
    startup
    My concerns are I read the following in one of the Oracle forums there:

    "If your US7ASCII database contains ASCII only, i.e. no illegal codes greater than 127, then you should not lose anything. If you store non-ASCII codes in the database, some extra steps may be necessary to save.... "

    How would I know if I ever had this???

    The other thing is will a cold backup do or do I need to export the database as well?

    Do I need to change any parameters in the init.ora file?

    Should I also change the National character set?

    Where can I get a list of the different charsets (and what they mean/include)?

    I have considered changing from US7ASCII to WE8ISO8859P1, but not sure if this is the best (i.e. include all the special symbols etc), since I don't want to do this again.


    Thanks once again.
    Nirasha

Posting Permissions

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


Click Here to Expand Forum to Full Width