Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01843 on a simple query (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
ORA-01843 on a simple query [message #674299] Thu, 17 January 2019 12:00 Go to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Hi all,

I am getting this error, although I cannot understand why, because as far as I can see the format mask seems correct,
I would expect the client to display the records per the sessions default , but not invoke an error...


SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------
18-JAN-2019

SQL>
SQL> select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;
select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual
                                                      *
ERROR at line 1:
ORA-00911: invalid character


SQL> show user
USER is "MYUSER"
SQL>




Any ideas where is it coming from, what am I missing ?

TIA
Andrey
Re: ORA-01843 on a simple query [message #674300 is a reply to message #674299] Thu, 17 January 2019 12:05 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Andrey_R wrote on Thu, 17 January 2019 20:00
Hi all,

I am getting this error, although I cannot understand why, because as far as I can see the format mask seems correct,
I would expect the client to display the records per the sessions default , but not invoke an error...


SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------
18-JAN-2019

SQL>
SQL> select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;
select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual
                                                      *
ERROR at line 1:
ORA-00911: invalid character


SQL> show user
USER is "MYUSER"
SQL>




Any ideas where is it coming from, what am I missing ?

TIA
Andrey


Ok, I found why this is happening, I think.

It's because the language in my session is not english, and therefore the MON synthax does not know what JAN means.

A good explanation with example can be found here:

http://surachartopun.com/2008/12/todateoctmon-ora-01843-not-valid-month.html

All the best and thanks,
Andrey
Re: ORA-01843 on a simple query [message #674301 is a reply to message #674300] Thu, 17 January 2019 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, that's not that the error means, you have an error because you have 2 statements in a single line.

[Updated on: Fri, 18 January 2019 04:22]

Report message to a moderator

Re: ORA-01843 on a simple query [message #674303 is a reply to message #674301] Fri, 18 January 2019 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
That link is talking about ORA-01843 Not a valid month
You're getting ORA-00911 invalid character
And sqlplus is pointing to the semi-colon.

It's what Michel says.

Did you accidentally paste the query twice into sqlplus?
Re: ORA-01843 on a simple query [message #674304 is a reply to message #674299] Fri, 18 January 2019 07:52 Go to previous messageGo to next message
joy_division
Messages: 4947
Registered: February 2005
Location: East Coast USA
Senior Member
And why are you stating that you got an 1843 error in your title?

And you do know that you don't have to set the NLS_DATE_FORMAT since you are giving an explicit format mask in the TO_DATE function, which of course you always should do. It doesn't matter what you NLS_DATE_FORMAT is.
Re: ORA-01843 on a simple query [message #674305 is a reply to message #674304] Fri, 18 January 2019 08:47 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

joy_division wrote on Fri, 18 January 2019 15:52
And why are you stating that you got an 1843 error in your title?

And you do know that you don't have to set the NLS_DATE_FORMAT since you are giving an explicit format mask in the TO_DATE function, which of course you always should do. It doesn't matter what you NLS_DATE_FORMAT is.
I have taken the example from a Chinese database.
It matters what is the NLS_LANGUAGE in this case, not the date format alone.

And I have made a mistake when copy-pasting ( I did the test case many times before while receiving ORA-01843 ).

Now I cannot connect to that shop until next week, and I can't fix the original post.

Sorry bout that.
Re: ORA-01843 on a simple query [message #674306 is a reply to message #674305] Fri, 18 January 2019 08:50 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

I can imitate the same situation with Arabic, for example, because in this language - "JAN" is not the abbreviation for the MON mask:

SQL> alter session set nls_language='arabic';

Session altered.

SQL>
SQL>
SQL>  select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;
 select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual
                *
ERROR at line 1:
ORA-01843: ┐┐┐ ┐┐┐ ┐┐┐┐


SQL>


In English it is, so it works:

SQL> alter session set nls_language='english';

Session altered.

SQL>
SQL>  select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;

TO_DATE('
---------
02-JAN-01

SQL>


Re: ORA-01843 on a simple query [message #674307 is a reply to message #674303] Fri, 18 January 2019 08:56 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Thu, 17 January 2019 21:00

No, that's not that the error means, you have an error because you have 2 statements in a single line.

That's correct, I didn't notice it until too late. Thanks for noting.

cookiemonster wrote on Fri, 18 January 2019 11:45
That link is talking about ORA-01843 Not a valid month
You're getting ORA-00911 invalid character
And sqlplus is pointing to the semi-colon.

It's what Michel says.

Did you accidentally paste the query twice into sqlplus?
Yes, I have..sorry Smile

[Updated on: Fri, 18 January 2019 08:57]

Report message to a moderator

Re: ORA-01843 on a simple query [message #674308 is a reply to message #674306] Fri, 18 January 2019 09:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
That's why when working in "global" environment you should use TO_DATE third parameter:

SQL> alter session set nls_language='finnish'
  2  /

Session altered.

SQL> select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;
select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual
               *
ERROR at line 1:
ORA-01843: virheellinen kuukausi


SQL> select to_date('02-JAN-0001', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') from dual;

TO_DATE('02-
------------
02-TAMMI -01

SQL> 

SY.
Re: ORA-01843 on a simple query [message #674309 is a reply to message #674308] Fri, 18 January 2019 09:25 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Solomon Yakobson wrote on Fri, 18 January 2019 17:18
That's why when working in "global" environment you should use TO_DATE third parameter:

SQL> alter session set nls_language='finnish'
  2  /

Session altered.

SQL> select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual;
select to_date('02-JAN-0001', 'DD-MON-YYYY') from dual
               *
ERROR at line 1:
ORA-01843: virheellinen kuukausi


SQL> select to_date('02-JAN-0001', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') from dual;

TO_DATE('02-
------------
02-TAMMI -01

SQL> 

SY.
Good idea. Many thanks !
Re: ORA-01843 on a simple query [message #674310 is a reply to message #674309] Fri, 18 January 2019 10:22 Go to previous message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another good idea: read the other topics. Wink
I posted something using this third parameter a couple of days ago: http://www.orafaq.com/forum/mv/msg/205441/674175/#msg_674175 (see line 11) and many warned people about month names and language (the use of site "Search" would give you several topics about your error like:
http://www.orafaq.com/forum/m/671782/?srch=ORA-01843#msg_671782
http://www.orafaq.com/forum/m/668792/?srch=ORA-01843#msg_668792
http://www.orafaq.com/forum/m/660661/?srch=ORA-01843#msg_660661
http://www.orafaq.com/forum/m/656714/?srch=ORA-01843#msg_656714
... Wink

Previous Topic: Why Do You Need to File a DBA and the Simplest Way of Filing It.
Next Topic: How to simulate crash database?
Goto Forum:
  


Current Time: Sun Jul 21 01:46:05 CDT 2019