Getting the NLS_LANG right for exp and imp

If you use the old-fashioned imp en exp tools, you really need to set the NLS_LANG environment variable right. To what value? It can be read from the v$nls_parameters. To make life easier, I always keep this set_nls.sql script available. It creates just the right (Linux) statement to make the setting.


set heading off
set feedback off
 select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
  from v$nls_parameters lan,
       v$nls_parameters ter,
       v$nls_parameters chr
  where lan.parameter='NLS_LANGUAGE'
    and ter.parameter='NLS_TERRITORY'
    and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives me for example:

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Just copy it in your shell environment and fire up the imp or exp!

Correct NLS_LANG for exports

It's important to set the right NLS_LANG environment variable when doing exports/imports.
The following query makes it easy getting the syntax and values right.

set heading off
set feedback off
select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
from v$nls_parameters lan,
v$nls_parameters ter,
v$nls_parameters chr
where lan.parameter='NLS_LANGUAGE'
and ter.parameter='NLS_TERRITORY'
and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives the line (e.g.)
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

which can easily be copied before the imp/exp is run.
For windows environments, you would need to replace 'export' by 'set' in the query.

Enjoy!