NLS_LANG in a select statement

[bgreen]

Hi,

Is there a way I can use the NLS_LANG is a sql statement to translate english to canadian frech?

 

[LKBrwnDBA]

NLS_LANG does not provide for translating text stored as data in a table.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

BGreen,

What do you want translated? If your translation needs are DATE related, then Oracle can translate Day names and Month names on the fly (at least into French) via the SQL command:

alter session set nls_language = 'French';

Session altered.

select last_name, to_char(start_date,'fmDay, Month dd, yyyy')Start_Date
from s_emp;

LAST_NAME                 START_DATE
------------------------- ---------------------------
Velasquez                 Mercredi, Février 1, 2006
Ngao                      Jeudi, Mars 8, 1990
Nagayama                  Lundi, Juin 17, 1991
Quick-To-See              Samedi, Avril 7, 1990
Ropeburn                  Dimanche, Mars 4, 1990
Urguhart                  Vendredi, Janvier 18, 1991
Menchu                    Lundi, Mai 14, 1990
Biri                      Samedi, Avril 7, 1990
Catchpole                 Dimanche, Février 9, 1992
Havel                     Mercredi, Février 27, 1991
Magee                     Lundi, Mai 14, 1990
Giljum                    Samedi, Janvier 18, 1992
Sedeghi                   Lundi, Février 18, 1991
Nguyen                    Mercredi, Janvier 22, 1992
Dumas                     Mercredi, Octobre 9, 1991
Maduro                    Vendredi, Février 7, 1992
Smith                     Jeudi, Mars 8, 1990
Nozaki                    Samedi, Février 9, 1991
Patel                     Mardi, Août 6, 1991
Newman                    Dimanche, Juillet 21, 1991
Markarian                 Dimanche, Mai 26, 1991
Chang                     Vendredi, Novembre 30, 1990
Patel                     Mercredi, Octobre 17, 1990
Dancs                     Dimanche, Mars 17, 1991
Schwartz                  Jeudi, Mai 9, 1991

But if you want your data translated, there are other (non-Oracle) options for you to explore.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bgreen]

I am looking to translate dollar amounts into french words.

 

[SantaMufasa]

So, BGreen, your need is not necessarily to translate English into Canadian French, but rather to translate Numbers into Canadian French, right?

If so, I believe we can come up with a solution. Please confirm.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[LKBrwnDBA]

Solution posted on this link

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[bgreen]

Yes that is correct. I need numbers translated to canadian french. If you have something for me to use I would gladly take it.

 

[SantaMufasa]

B,

How do "Canadian French" numbers differ from "French French" numbers? Once I iron out that issue (and resolve a couple of "paying-customer" issues today), I'll be delighted to enhance my user-defined function to handle your specific need (along with Spanish and, perhaps, German and maybe Ebonics and Utahnics, as well. <grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bgreen]

I am not sure. I am providing a number like so:

123.45

This need to be translated to french words. Thanks for you help.

 

[SantaMufasa]

...and one more question, B, what units of currency do you wish to appear?...

US Dollars/cents: "Cent vingt-trois dollars et quarante-cinq cents."

Canadian Dollars/cents: "Cent vingt-trois dollars canadiens et quarante-cinq cents."

Francs/centimes: "Cent vingt-trois francs et quarante-cinq centimes."

Euro/cents/Euro-centimes/Euro-pfennigs: "Cent vingt-trois Euros et quarante-cinq Euro-centimes.

...or something different?

Let me know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bgreen]

We would want canadian french so it we would want it to appear as the following:

UN CENT VINGT-TROIS ET 45/100

 

[SantaMufasa]

So you don't want any mention of currency and you don't want spelled decimal amounts?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bgreen]

That is correct.

 

[bgreen]

I have figured out a way to do this leveraging off current Oracle packeges and views. If you are interested let me know.

 

[SantaMufasa]

Sure...if it will save tailoring existing code, I'd be eager to see it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.