sesstion setting in an oracle script

I am using number of scripts that have  these three lines?
Any idea what they do or mean?

ALTER SESSION SET nls_territory = AMERICA;
ALTER SESSION SET nls_Language = AMERICAN;
SET DEFINE OFF;
ALad2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
well, yes, first, change your country settings for date, time, numeric characters

second is the language in which things are reported in

set define off is to disable the & substitutions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
you can see what happens when you run date statements:

19:51:43 >alter session set nls_territory = 'AMERICA';

Session altered.

19:51:46 >select sysdate from dual;

SYSDATE
---------
12-FEB-18

19:51:50 >alter session set nls_territory = 'FRANCE';

Session altered.

19:51:56 >select sysdate from dual;

SYSDATE
--------
12/02/18

19:51:58 >

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ALad2005Author Commented:
Thank you guys.
0
ALad2005Author Commented:
So Cool Geert Gruwez!!  Thank you for showing me that.   Now it makes sense!
for Language setting, I guess it works similar way but I need to use different sql statement to see the difference?
0
slightwv (䄆 Netminder) Commented:
There are typically examples in the docs as well.  In the link I posted:

Examples
 
See these examples of using the NLS_LANGUAGE parameter:
•For an example of setting NLS_LANGUAGE to Italian, see Oracle Database Globalization Support Guide.

•For an example of overriding default values for NLS_LANGUAGE and NLS_TERRITORY during a session, see Oracle Database Globalization Support Guide.

Which gets you here:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/setting-up-globalization-support-environment.html#GUID-E423D89E-9C74-46E4-9B88-EE65A1114EF4
0
Geert GOracle dbaCommented:
If you want to see a language effect, just try an invalid statement.
The error will be given in the language you set.

select silly from samples

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>The error will be given in the language you set.

Perfect example of that exact scenario in the last link I posted.

The value specified for NLS_LANGUAGE in the initialization parameter file is the default for all sessions in that instance. For example, to specify the default session language as French, the parameter should be set as follows:

NLS_LANGUAGE = FRENCH

Consider the following server message:

ORA-00942: table or view does not exist

When the language is French, the server message appears as follows:

ORA-00942: table ou vue inexistante
0
ALad2005Author Commented:
You guys are awesome!!!
Thank you for sharing your vast knowledge of oracle in understandable language!!!
0
ALad2005Author Commented:
Great documentation link Slightw!!!
sorry this leads another question, how can I check what languages are available?
Is there a way to check this?
Thanks in advance.
0
slightwv (䄆 Netminder) Commented:
>>how can I check what languages are available?

Yep.  Did you poke around the Globalization Support Guide to see what all it has?

Normally lists can be found in the appendix:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/appendix-A-locale-data.html#GUID-A9E30C27-FD47-4552-B670-F41A95B11405

Remember that available doesn't mean installed.  If you want a specific language you need to make sure it was installed with the software.
0
ALad2005Author Commented:
Got it !!! Thank you so much Slightvw!  You answered all my questions to fullest  !
0
Geert GOracle dbaCommented:
one small thing you may find in the doc links, but need to be aware of ...
make sure your database character set supports the languages you need to support

easiest is to support all by choosing the unicode characterset: AL32UTF8

see chapter 2 of the globalization support guide.

that's the basis of the whole thingy
0
ALad2005Author Commented:
Thank you so much Slightwv  and Geert for your help.  I got much more than I asked for !!! so that's really great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.