ORA-01846: not a valid day of the week

Monday, August 25, 2008

We are using external table to read alert_log file so I've tried to use script for monitoring errors querying that table.

That script filters date pattern from alert_log file and converts this date from ASCI to Oracle date format.
Filtered characters are converted with to_date function.
After a meanwhile I ran into error:


ERROR at line 2:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01846: not a valid day of the week


Error showed up because default language on database machine was "AMERICAN" and "CROATIAN" on my notebook.



From alert_log i get this output:
system@TEST11> select * from alert_log
2  where rownum < 2;

LINE
-----------------------------------------
Sun Aug 24 06:00:00 2008

1 row selected.
On my notebook:
system@TEST11> select to_char(to_date('24.08.2008 06:00:00','dd.mm.yyyy hh24:mi:ss')
,'Dy Mon DD hh24:mi:ss yyyy') dateformat
2  from dual;

DATEFORMAT
------------------------
Ned Kol 24 06:00:00 2008

1 row selected.
As you can see these are completely different names for months and days. - Sun Aug 24 06:00:00 2008 - Ned Kol 24 06:00:00 2008
system@TEST11> select case when line like '___ ___ __ __:__:__ ____'
2           then to_date(line, 'Dy Mon DD hh24:mi:ss yyyy')
3         else null
4         end dateformat
5   from alert_log
6   where rownum<2;
then to_date(line, 'Dy Mon DD hh24:mi:ss yyyy')
*
ERROR at line 2:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01846: not a valid day of the week
Script tries to convert date written on english language to date written on croatian language and that confused Oracle. So I've helped myself using NLS_DATE_LANGUAGE=AMERICAN parameter in to_date function.
system@TEST11> select case when line like '___ ___ __ __:__:__ ____'
2 then to_date(line, 'Dy Mon DD hh24:mi:ss yyyy','NLS_DATE_LANGUAGE = AMERICAN')
3 else null
4 end dateformat
5 from alert_log
6 where rownum<2;

DATEFORMAT
-------------------
24.08.2008 06:00:00

1 row selected.
As you can see now everything went okey without "ORA-01846: not a valid day of the week" error.


Share/Bookmark

0 comments:

Post a Comment