nls characters

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

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?

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

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.

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