Home » SQL & PL/SQL » SQL & PL/SQL » Charater set (Oracle Database, 12.1.0.2,Windows)
Charater set [message #673621] Tue, 27 November 2018 22:09 Go to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
I have a database that is outputting dollar amount as $17,10 instead of $17.10. It seems like the DB was recently migrated from a European country to the US but I am not too sure. Will this be due to the character set? How do I go about fixing this issue? Thanks for your help!
Re: Charater set [message #673622 is a reply to message #673621] Tue, 27 November 2018 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9532776700346468197
Re: Charater set [message #673658 is a reply to message #673622] Wed, 28 November 2018 08:37 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
The NLS parameters are set at the database creation and may NOT be changed. However if you make the following logon trigger as the SYS user everyone will use the american NLS parameters

CREATE OR REPLACE TRIGGER logon_nls_trigger
AFTER LOGON
ON DATABASE
BEGIN
execute immediate 'alter session set nls_territory=''AMERICA''';
END ;
/
Re: Charater set [message #673662 is a reply to message #673658] Wed, 28 November 2018 09:39 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Bill B wrote on Wed, 28 November 2018 14:37
The NLS parameters are set at the database creation and may NOT be changed.
That's not necessarily the case in certain conditions. See the following Metalink article here
Re: Charater set [message #673664 is a reply to message #673662] Wed, 28 November 2018 12:42 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
That is true, you Can't change the database NLS settings, but you can change the instance settings (running version) but it requires a reboot of the system. The logon trigger will just set every session that logs on to the version that you selected.

Changing the Instance settings or the session settings will both work.
Re: Charater set [message #673665 is a reply to message #673662] Wed, 28 November 2018 12:44 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
gazzag, that link is for something to do with XMLtype updating via a trigger.
Re: Charater set [message #673666 is a reply to message #673658] Wed, 28 November 2018 17:09 Go to previous messageGo to next message
youngb912
Messages: 56
Registered: October 2007
Location: New York
Member
Thank you very much for the responses. I found out this evening that the DB NLS parameter is appropriately set. It's the application server that has the unwanted setting. Issues resolved by pointing to another app server based in the US instead of the one based in the EU.

[Updated on: Wed, 28 November 2018 17:09]

Report message to a moderator

Re: Charater set [message #673668 is a reply to message #673665] Thu, 29 November 2018 02:38 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
joy_division wrote on Wed, 28 November 2018 18:44
gazzag, that link is for something to do with XMLtype updating via a trigger.
"Use Scanner Utility before Altering the Database Character Set (Doc ID 123670.1)"
Re: Charater set [message #673673 is a reply to message #673668] Thu, 29 November 2018 12:06 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
That's weird...now the link works properly.

And I think the OP is talking about regional settings, not characterset anyways. To me, whether you use a comma or period to distinguish thousands and pennies is regional, not characterset.

[Updated on: Thu, 29 November 2018 12:08]

Report message to a moderator

Re: Charater set [message #673675 is a reply to message #673673] Fri, 30 November 2018 06:27 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Good point.
Previous Topic: Fetching data dynamically in Collections
Next Topic: How to use Optimistic locking in my Package
Goto Forum:
  


Current Time: Mon Sep 21 11:37:48 CDT 2020