1

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

Mark Szente
  • 21
  • 2
  • 5

2 Answers2

2

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
Vincent
  • 395
  • 1
  • 9
  • Hi Vincent! Thank you for the answer, it works fine if I want to set month language in SQLPlus, but still I have the same error with the import tool. – Mark Szente Oct 12 '11 at 10:16
  • You could edit the value in the windows registry (HKLM/SOFTWARE/Oracle/[your oracle home]/NLS_LANG) – Vincent Oct 12 '11 at 11:35
  • Still not working. When I have both environment variables and the registry set, the environment variables take effect, when I remove the environment variables, the registry setting itself doesn't effect the settings, even in the SQLPlus. I even restarted Windows to avoid registry value caching. – Mark Szente Oct 12 '11 at 14:53
  • It could be that even if you set NLS_LANG correctly, it won't set the NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT correctly. Is the failing statement a CREATE TABLE? If it is, could you run the statement before the import? – Vincent Oct 13 '11 at 16:23
0

You need to set the NLS_ environment for both the exp command and the imp command in future.

To use your existing dump, try to restore the exact environment settings used during the past exp session, but still, if I recall correctly, you may hit quite a few bugs with dumps created with non-English NLS settings being totally unusable (i.e. not importable even if the NLS_ settings match those used by exp).

kubanczyk
  • 13,502
  • 5
  • 40
  • 55