Home » Infrastructure » Unix » using single database for two different languages
using single database for two different languages [message #272971] Mon, 08 October 2007 07:40 Go to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
hi
Iam into a banking project, we have our frontend which accepts english characters an stores in our database.
ours in oracle 9i/10g in solaris, now we need to insert,store and retrive cyrillic characters as well.
we want the exsisting database to b used without changing the stored informations.
i understan tat v need to alter nls parameters.but how to make the database changes permanent and make my esitsing database work for both english as well as cyrillic characters


Re: using single database for two different languages [message #272979 is a reply to message #272971] Mon, 08 October 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to change your database character set.
This implies you recreate the database unless your current database character set is US7ASCII.

Regards
Michel
Re: using single database for two different languages [message #272985 is a reply to message #272979] Mon, 08 October 2007 08:38 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
tnx michel

but . . i have the earlier data stored in it.

will it get affected?
and since the network is huge and iam not the dba. if i have to request dba doin it, wat should i request for?
these are the current NLA parameters
utput:
PARAMETER VALUE
------------------------------ ---------------------------------

-------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY CZECH REPUBLIC
NLS_CURRENCY Kc
NLS_ISO_CURRENCY CZECH REPUBLIC
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF

PARAMETER VALUE
------------------------------ ---------------------------------

-------
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY Kc
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Re: using single database for two different languages [message #272986 is a reply to message #272979] Mon, 08 October 2007 08:40 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
i have changed it to czeh republic.

but , what should b the character set for it and , if i cange it and then retrive the data earlier stored will it b affected?

plz guide me thro

Re: using single database for two different languages [message #272988 is a reply to message #272979] Mon, 08 October 2007 08:46 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
initially plz answer my queries

1) my character set is US7ASCII. but all the tables r created using char,varchar,number and not nchar,nvarchar. so do i ahve to alter all the tables? thr would b around 200 tables.

2) and in solaris does any change required? if so can i being ac client do it ?

3) v have all the proc programs running for the exsisting database, how to modify them ? without affecting the exsisting database?

it would b very helpful if u guide me thro step by step
Re: using single database for two different languages [message #272994 is a reply to message #272988] Mon, 08 October 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Repost without using IM speak.
Please read and follow OraFAQ Forum Guide.

Regards
Michel
Re: using single database for two different languages [message #272995 is a reply to message #272994] Mon, 08 October 2007 09:21 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
Hi

sorry for the inconvenience!
the character set that currently exists is US7ASCII.
and we have all the data in English till now.

initially can you please answer my queries

1) my character set is US7ASCII. but all the tables are created using char,varchar,number and not nchar,nvarchar. so do i have to alter all the tables? there would be around 200 tables.

2) and in solaris does any change required? if so can i being ac client do it ?

3) we have all the proc programs running for the existing database, how to modify them ? without affecting the existing database?

it would be very helpful if u guide me through step by step

Re: using single database for two different languages [message #273004 is a reply to message #272995] Mon, 08 October 2007 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case:
- to change the character set of your database you just have to choose a character set that fit your all the characters you want to use (see Globalization Support Guide) and issue an "alter database" (have a look to this in documentation) BUT you MUST previously run "csscan" (have a look to this in documentation) to verify there is no problem.
- THEN change the client and server NLS parameters accordingly to the character set used.

Regards
Michel


Re: using single database for two different languages [message #273015 is a reply to message #273004] Mon, 08 October 2007 12:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The client NLS parameters don't necessarily have to match the database settings, but do need to be compatible with it. If you are going to change the database characterset, AL32UTF8 is a good starting point to look at.

Beware - if your database characterset matches your client nls setting, then no characterset conversion will take place becuase Oracle trusts your client to only use valid byte values. Using this fact, you can insert and retrieve any byte values. This means that your client may be bale to insert single byte Cyrillic characters and retrieve them successfully, but the non 7-bit characters will be stored in the wrong location so any other non US7ASCII client will see those characters as garbage.
Re: using single database for two different languages [message #273046 is a reply to message #273004] Mon, 08 October 2007 22:52 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
Thanks Michel,
And server NLS parameters cannot be changed without having sys privileges, right?
should I ask DBA to do it or is there any other way that we can do it directly without affecting the database maintained
Re: using single database for two different languages [message #273091 is a reply to message #273015] Tue, 09 October 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

If you are going to change the database characterset, AL32UTF8 is a good starting point to look at.

I beg to differ.
AL32UTF8 is the END point to look at when nothing else works.
AL32UTF8 is a varying size character set.
It is harder to manage especially space.
It uses much more space when you can also use a signle byte character set.

If OP only needs american (us7ascii) and cyrillic characters then he can choose one the EE8% character set (the one that is appropriate to his languages).
If none is available for the set of languages he wants to manage, then AL32UTF8 is the choice.

Regards
Michel
Re: using single database for two different languages [message #273093 is a reply to message #273046] Tue, 09 October 2007 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

server NLS parameters cannot be changed without having sys privileges, right?

The most important is not server character set but DATABASE one. And there you need to be a DBA to change it.

NLS parameters have to set at 3 levels:
- database
- instance
- client, this includes server when it acts as a client (for instance during a batch that executes on it)

Regards
Michel

[Updated on: Tue, 09 October 2007 01:38]

Report message to a moderator

Re: using single database for two different languages [message #273100 is a reply to message #273093] Tue, 09 October 2007 01:55 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
Thanks.

now Iam writing a proc code to give input to my table

can you please help me write a pro*c code instead of procedure to insert cyrillic characters to my table

Regards
yazhini
Re: using single database for two different languages [message #273120 is a reply to message #273100] Tue, 09 October 2007 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference between cyrillic or other characters, they are just bytes, just set your client NLS_LANG to the character set you use.

If you want a deeper answer on Pro*C, create a new topic in Precompilers... forum.

Regards
Michel
Re: using single database for two different languages [message #273121 is a reply to message #273120] Tue, 09 October 2007 02:28 Go to previous messageGo to next message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
Thank you.

will do the same

regards,
yazhini
Re: using single database for two different languages [message #273270 is a reply to message #273121] Tue, 09 October 2007 15:48 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In the US - AL32UTF8 is maybe the BEST characterset to consider as it can handle "any" character and the vast majority of characters used in US text fall into the 7-bit ASCII range (single byte storage). This means that double or triple byte storage is the exception and consequently choosing UTF8 has virtually zero impact on storage usage (assuming that's important to you). I'm unfamiliar with cyrillic - but yes, it probably uses lots of multi-byte storage and for "big" databases would be a bigger consideration.

In choosing a single byte database characterset you need to double check what characters it supports and what you conceivably want to store. e.g. Euro sign? Try avoid having to use different charactersets if possible.
Re: using single database for two different languages [message #273295 is a reply to message #273270] Tue, 09 October 2007 21:56 Go to previous message
yazhini.t
Messages: 13
Registered: October 2007
Junior Member
thank you for the information

will try it and let you know about it.

regards,
yazhini
Previous Topic: EM agent issue
Next Topic: find command
Goto Forum:
  


Current Time: Fri Mar 29 08:45:23 CDT 2024