HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Set encoding from within SQL*Plus

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlwithinplusencodingfromset

Problem

I have a large set of files with SQL commands that I run with a SQL*Plus script that uses the @@ operator. I run the script on third-party computers (or even send it by e-mail) so I'd like to make it as self-contained as possible.

Files are encoded as Win-1252. Currently, I need to set the NLS_LANG environment variable before running the script:

C:\> SET NLS_LANG=SPANISH_SPAIN.WE8MSWIN1252

C:\> echo exit | sqlplus foo/bar@//example.com/xe @install.sql


This works but I wonder if it's possible to change the client encoding with a SQL or SQL*Plus command. (Or at least read the value and abort execution if it doesn't match.)

Solution

NLS_LANG can't be changed from inside a session, however other settings can.

You can't change the character set once a database connection has been established (ie: the 2nd part of NLS_LANG), but you can change the language with:

alter session set NLS_LANGUAGE=SPANISH


... and the territory with:

alter session set NLS_TERRITORY=SPAIN

Code Snippets

alter session set NLS_LANGUAGE=SPANISH
alter session set NLS_TERRITORY=SPAIN

Context

StackExchange Database Administrators Q#36170, answer score: 6

Revisions (0)

No revisions yet.