Home » SQL & PL/SQL » SQL & PL/SQL » Displaying output (merged)
Displaying output (merged) [message #297630] Fri, 01 February 2008 17:04 Go to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
I am just stuck with SQLPlus.

For example: select to_char(total,'XXXX') from sales;

Does anyone know the NLS_currency_code for GBP?
Whatever that I put on XXXX will display result:
GBP 23.00
rather than just 23.00
Re: Result display OUTPUT [message #297631 is a reply to message #297630] Fri, 01 February 2008 17:06 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
select 'GBP ' || to_char(total,'XXXX') from sales;
Displaying output [message #297635 is a reply to message #297630] Fri, 01 February 2008 18:33 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Hi there, I need to use alter session on nls_iso_currency

When I do:
alter session set NLS_ISO_CURRENCY = JAPAN;

and when I did to_char(total,'C099G999D99')
my output is JPY23.00, JPY 23.00 which is GREAT..

But I want the output to be GBP23.00.

So what I did was:
alter session set NLS_ISO_CURRENCY = UK;

and I got this error message:
ORA-12705: Cannot access NLS data files or invalid environment specified

ALSO, I tried alter session set nls_currency = '';
but on the NLS_CURRENCY value, it gives me ?

And here are all my session parameters:

PARAMETER VALUE

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY JAPAN
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Please let me know where I did wrong and how do I fix this?
Re: Result display OUTPUT [message #297655 is a reply to message #297630] Sat, 02 February 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at SQL Reference, section Number Format Models, you'll find among others L format element.

Regards
Michel
Re: Displaying output [message #297657 is a reply to message #297635] Sat, 02 February 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost, post all information in the same topic, it would be easier to answer you if you don't scatter the information among several forums.

Regards
Michel
Re: Displaying output [message #297658 is a reply to message #297635] Sat, 02 February 2008 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
alter session set NLS_ISO_CURRENCY = UK;

and I got this error message:
ORA-12705: Cannot access NLS data files or invalid environment specified

Query V$NLS_VALID_VALUES to know what are the allowed values for your environment.

Regards
Michel
Re: Displaying output (merged) [message #297673 is a reply to message #297630] Sat, 02 February 2008 04:54 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Have a look at SQL Reference, section Number Format Models, you'll find among others L format element.

I tried that and it didn't mention anything about nls_iso_currency for United Kingdom. I tried to search for V$NLS_VALID_VALUES but can't seem to find the right environment for United Kingdom.

I just want to know the Value for United Kingdom and then see if I can alter the session for the Parameter name (LANGUAGE | SORT | TERRITORY | CHARACTERSET) so that I can add the 'GBP' sign on the total amount. Please help!!
Re: Displaying output (merged) [message #297674 is a reply to message #297630] Sat, 02 February 2008 05:28 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
select * from v$nls_valid_values
where parameter='TERRITORY'
/


run it to watch all valid territory.
as i checked it was united kingdom
so,replace uk with 'UNITED KINGDOM'.
alter session set NLS_ISO_CURRENCY ='UNITED KINGDOM'; 


Re: Displaying output (merged) [message #297678 is a reply to message #297630] Sat, 02 February 2008 06:27 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Thanks.. that's help.. when I did the alter session and the to_char character:

(total,'C099G999D99')
However, I get an output of GBP001,970.00.
How come there is 00 after GBP?
Previously it was 1,970.00 but now it became GBP001,970.00

How can I change it to just GBP1,970.00?
Re: Displaying output (merged) [message #297682 is a reply to message #297678] Sat, 02 February 2008 07:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Because of the 0 in 099
It will fill out the number with zeroes
Re: Displaying output (merged) [message #297685 is a reply to message #297630] Sat, 02 February 2008 07:24 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
So what do you recommend??? I mean is there a number file format that you can get rid of those 0s?
Re: Displaying output (merged) [message #297686 is a reply to message #297685] Sat, 02 February 2008 07:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Sat, 02 February 2008 14:16
Because of the 0 in 099
It will fill out the number with zeroes

zepalways wrote on Sat, 02 February 2008 14:24
So what do you recommend??? I mean is there a number file format that you can get rid of those 0s?


If your extra zeroes are caused by the zero in your number format and you want to get rid of the extra zeroes, GUESS what I would recommend..
Did you actually read (and study) the docs on format-masks?


Man: Doctor, my foot hurts when I walk
Doctor: that is because you have a stone in your shoe
Man: Doctor, what do you recommend?

Wink

[Updated on: Sat, 02 February 2008 07:29]

Report message to a moderator

Re: Displaying output (merged) [message #297814 is a reply to message #297630] Sun, 03 February 2008 22:09 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
Man: Doctor, my foot hurts when I walk
Doctor: that is because you have a stone in your shoe
Man: Doctor, what do you recommend?



well said, frank Laughing
Previous Topic: database triggers
Next Topic: regarding date
Goto Forum:
  


Current Time: Fri Dec 09 09:27:58 CST 2016

Total time taken to generate the page: 0.11767 seconds