-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|