ORA-01843 on a simple query [message #674299] |
Thu, 17 January 2019 12:00 |
Andrey_R
Messages: 441 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 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Andrey_R wrote on Thu, 17 January 2019 20:00Hi 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 #674303 is a reply to message #674301] |
Fri, 18 January 2019 03:45 |
cookiemonster
Messages: 13925 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 |
joy_division
Messages: 4963 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 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
joy_division wrote on Fri, 18 January 2019 15:52And 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 |
Andrey_R
Messages: 441 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 |
Andrey_R
Messages: 441 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:45That 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
[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 |
Solomon Yakobson
Messages: 3275 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 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Solomon Yakobson wrote on Fri, 18 January 2019 17:18That'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 !
|
|
|
|