0

When I execute "select * from SYS.NLS_INSTANCE_PARAMETERS" in SQL Developer, result includes GERMAN for NLS_LANGUAGE and GERMANY for NLS_TERRITORY. Now I need to change those values to AMERICAN respectively AMERICA permanently. How can this be done? There're so many documentations online, but I coulnd't find a simple guide. thanks for your help

user
  • 157
  • 2
  • 14

1 Answers1

1

Depending on if you are using a pfile or spfile. If using a pfile, edit the file and restart the database. If using an spfile (as you should be doing), then use the ALTER SYSTEM SET command, as documented here.

Why do you want to do this? What problem are you really trying to solve? Are you aware that even if you set this at the database level, clients can override it with ALTER SESSION?

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • We have three DB levels. In my opinion they all should have the same configuration. Currently test and production are having AMERICAN/AMERICA and integration GERMAN/GERMANY. The actual problem is, we send information out of the DBs to our customers, in test and production timestamps are in US syntax (AM/PM), timestamps in integration in european syntax (24h). We hope by adjusting integration to solve that issue. Only a few people are allowed to work on these environments, normally they don't use ALTER SESSION. – user Jul 14 '21 at 14:08
  • @user - if the format (and language) of data you are sending out matters, and it always does really, then you should not rely on the NLS settings anyway; format dates and timestamps with explicit format masks, and probably numbers with explicit decimal separators. Making your environments match isn't a bad thing, but don't rely on it - as Ed said, individual sessions can and will override the instance settings anyway. – Alex Poole Jul 14 '21 at 14:12
  • @Alex Poole - if I get you right, the safest way is to convert all columns with timestamps to the format you want before sending? that would be something like to_timestamp(date,'dd.MM.yyyy HH24:MI:SS') or covert(date as timestamp)? – user Jul 14 '21 at 14:36
  • @user - yes, but it would be with `to_char()` to get explicitly-formatted strings. (Assuming you're sending data as text, i.e. as flat files...) – Alex Poole Jul 14 '21 at 14:39
  • Agreeing and expanding on Alex - yes you really should have all 3 databases configured the same, and for more reasons than the current problem. But again, that does not address the fact that, ultimately, all NLS settings are under control of the client process. The system setting of those parameters is merely the _default_ that the client inherits if no other action is taken. – EdStevens Jul 14 '21 at 16:04
  • 1
    Also you seem confused about _formatting_ of DATE and TIMESTAMP data types. They are both internal, binary format. The functions TO_DATE and TO_TIMESTAMP take a _character string_ as input to the respective binary format. The TO_CHAR function takes a DATE or a TIMESTAMP as input and converts it to the desired, human-readable, character string. For more on this, read https://edstevensdba.wordpress.com/category/dealing-with-dates/ – EdStevens Jul 14 '21 at 16:07