Oracle import fails due to NLS_DATE_LANGUAGE setting

importoracle

I have an Oracle 10 database server and a dump created on another Oracle 10 server. When using the imp command to import the dump to the database:

imp 'sys/*** as sysdba' file=c:\[...]\strukt.dmp full=y log=c:\[...]\import.log

I get the error message "not a valid month" for the following date format:

'04-FEB-08 03.54.49.000000 PM +01:00'

After a little debugging I tried to reproduce the error in my SQLPlus client. The line

select to_date('04-FEB-08') from dual;

gives me the same error message. After a little research on the topic I also realized that the reason of the error message is my "NLS_DATE_LANGUAGE" setting.

NLS_DATE_FORMAT      RR-MON-DD
NLS_DATE_LANGUAGE    HUNGARIAN

I set the date language to "AMERICAN", and now all was fine with the SQLPlus command.

alter session set NLS_DATE_LANGUAGE='AMERICAN';

Now comes the problematic bit. I have no idea how to tell the import tool that it should use a different NLS setting.

The Oracle documentation tells me I should set it as an environment variable but it doesn't seem to work. The Oracle server is on a Windows 2008 box so I set the "NLS_DATE_LANGUAGE" environment variable to "AMERICAN" in Control Panel/System/Advanced/Environment variables, both for the current user and as a system variable, but still i have the same message.

I'm new to the Oracle world and I might be missing something obvious. If you have a solution or any workaround trick, I greatly appreciate it.

Thank you in advance,

Mark

Best Answer

You can set the environment variable NLS_LANG before executing imp or sqlplus. On windows system, if this environment variable is not set, Oracle will look into the windows registry.

C:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

C:\>sqlplus vnz/[...]

[...]

SQL> select to_char(sysdate, 'month') from dual;

TO_CHAR(S
---------
october

SQL> exit
Disconnected from Oracle Database 10g [...]

C:\>set NLS_LANG=FRENCH_FRANCE

C:\>sqlplus vnz/[...]

[...]

SQL> select to_char(sysdate, 'month') from dual;

TO_CHAR(S
---------
octobre
Related Topic