Thanks for the question, Evgueni.
Asked: February 26, 2019 - 5:57 pm UTC
Last updated: February 27, 2019 - 11:09 am UTC
Version: 12.2
Viewed 1000+ times
You Asked
Hello,
I am trying to use Access Advisor package in a 12.2 DB. When executing DBMS_ADVISOR.EXECUTE_TASK I get error:
begin
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at line 2
This behavior matches exactly the note 259188.1 (…the DBMS_ADVISOR.EXECUTE_TASK likely will error out with ORA-01843…). But this note has been last reviewed September 2015. Haven't any patch/workaround been released since?
with LiveSQL Test Case:
and Chris said...
The bug is still unfixed sadly :(
You could try looking for values in sys.WRI$_SQLSET_STATISTICS.first_load_time that are '0000-00-00/00:00:00' and making them null.
But as the note says:
The error occurs due to sys.WRI$_SQLSET_STATISTICS.first_load_time is '0000-00-00/00:00:00' for rows that the actual failing run of DBMS_ADVISOR.EXECUTE_TASK inserts in there, then later queries SELECT ... to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'), so updating sys.WRI$_SQLSET_STATISTICS.first_load_time manually with a meaningful date string before running DBMS_ADVISOR.EXECUTE_TASK does not work around the problem.
Is this answer out of date? If it is, please let us know via a Comment