ORA-01843: not a valid month

ORA-01843:

Not a valid month

Cause:

A date specified an valid month.

Valid month are: January-December, for format code MONTH, and Jan-Dec, for format code MON.

Action:

Enter a valid month value in the correct format.

Scenario One

The Oracle 10g on the server uses the Chinese character set, so you will be prompted when inserting according to the following date formatORA-01843.

SQL> Insert into auction values (4, 'canonical', 1,250, '27-oct-2002 ');
Insert into auction values (4, 'cannon cama', 1,250, '27-oct-2002 ')
ORA-01843: not a valid month

To solve this problem, you can modifyNLS_DATE_LANGUAGEAndNLS_DATE_FORMAT, The method is as follows:

SQL> alter session set nls_date_language = AMERICAN;
Session altered

SQL> alter session set nls_date_format = 'dd-MON-YYYY ';
Session altered

Test the preceding insert statement again.

SQL> Insert into auction values (4, 'canonical', 1,250, '27-oct-2002 ');
1 row inserted

Note:

Because only the current session'sNLS_DATE_LANGUAGEAndNLS_DATE_FORMATSo useShow parametersThe two parameter settings are not changed.

SQL> show parameters NLS_DATE_LANGUAGE
NAME TYPE VALUE
-------------------------------------------------------------------
Nls_date_language string

SQL> show parameters NLS_DATE_FORMAT
NAME TYPE VALUE
-------------------------------------------------------------------
Nls_date_format string