Unable to delete extended calendar CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_CALENDAR) violated
Article ID: 221405
Updated On:
Products
Issue/Introduction
Unable to delete Extended calendar. Calendar is not auto-generating new dates so was going to delete and re-add.
Tried to regenerate and got this error:
Enter Calendar Name: MONTH_END_CALENDAR
CAUAJM_I_50435 Regenerate dates Successful for Extended Calendar: MONTH_END_CALENDAR
CAUAJM_E_18416 Event Server: <ATSYSPRD> Failed Query: <INSERT INTO ujo_calendar (name,cal_id,day) VALUES ('MONTH_END_CALENDAR',2,TO_DATE('08/31/2021 00:00','MM/DD/YYYY HH24:MI'))>
CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_CALENDAR) violated
CAUAJM_I_18403 Processing OCI function OEXEC, OEXN(3)
CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.
CAUAJM_E_18401 Function <doExecute> invoked from <execute> failed <908>
CAUAJM_E_18416 Event Server: <ATSYSPRD> Failed Query: <INSERT INTO ujo_calendar (name,cal_id,day) VALUES ('MONTH_END_CALENDAR',2,TO_DATE('08/31/2021 00:00','MM/DD/YYYY HH24:MI'))>
CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_CALENDAR) violated
checked the DB and see this..
QL> select cal_id,name from ujo_calendar where name ='MONTH_END_CALENDAR';
CAL_ID NAME
---------- ----------------------------------------------------------------
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
0 MONTH_END_CALENDAR
CAL_ID NAME
---------- ----------------------------------------------------------------
0 MONTH_END_CALENDAR
12 rows selected.
Deleted successfully:
>autocal_asc -D -e MONTH_END_CALENDAR
CAUAJM_I_50427 Delete Successful for Extended Calendar: MONTH_END_CALENDAR
Tried to re-add, and got this..
autocal_asc -I MONTH_END_CALENDAR
CAUAJM_E_10601 Cannot insert calendar MONTH_END_CALENDAR, calendar name already exists.
CAUAJM_I_50424 Insert Failed for Extended Calendar: MONTH_END_CALENDAR
Tried with -F and got this:
autocal_asc -I MONTH_END_CALENDAR -F
CAUAJM_E_10518 Failed to modify extended calendars, calendar does not exist.
CAUAJM_I_50424 Insert Failed for Extended Calendar: MONTH_END_CALENDAR
Tried to delete again, and got this:
autocal_asc -D -e MONTH_END_CALENDAR
CAUAJM_I_50428 Delete Failed for Extended Calendar: MONTH_END_CALENDAR
CAUAJM_E_50309 Extended calendar MONTH_END_CALENDAR does not exist.
And the above DB entry is still in place after deleting. How can we get this removed and how will it affect jobs that are tied to this calendar??
Calendar Def:
Extended
extended_calendar: MONTH_END_CALENDAR
description: "Last business day of the month. Run previous business day if a holiday."
workday: mo,tu,we,th,fr
non_workday:
holiday: P
holcal: ML_HOLIDAY
cyccal:
adjust: 0
condition: EOMWORK
The standard calendar that was generated:
calendar: MONTH_END_CALENDAR
description: "Last business day of the month. Run previous business day if a holiday."
01/31/2021 00:00:00
02/28/2021 00:00:00
03/31/2021 00:00:00
04/30/2021 00:00:00
05/31/2021 00:00:00
06/30/2021 00:00:00
07/31/2021 00:00:00
08/31/2021 00:00:00
09/30/2021 00:00:00
10/31/2021 00:00:00
11/30/2021 00:00:00
12/31/2021 00:00:00
Resolution
The product should hot have allowed a standard and extended calendar to exist with the same names. Our Engineering team is working on code changes to better prevent that. If you know the exact process that was followed which allowed for two calendars to be created with the same names please share that with us so our SE team can make sure to lock that process down as well, if it differs with the test cases they are already aware of.
To move forward, you will need to delete the calendar (both extended and standard) manually via the database and then add back what you need making sure each name is unique, regardless if they are different types.
And potentially re-save job definitions if needed to get them back on schedule.
---
To delete the calendar(s) dates (for both) and definition (for the extended calendar) :
delete from ujo_ext_calendar where name='ML_CALENDAR_MONTH_END';
delete from ujo_calendar where name='ML_CALENDAR_MONTH_END';
---
Query the other extended calendars to see if any referenced the "ML_CALENDAR_MONTH_END" as its holiday calendar. It the query does show any you will need to review that calendar and any jobs using that calendar as well.
select name from UJO_EXT_CALENDAR where as_holcal='ML_CALENDAR_MONTH_END';
---
Query the ujo_jobst view to identify any jobs that reference the calendar "ML_CALENDAR_MONTH_END"
select job_name from ujo_jobst where run_calendar='ML_CALENDAR_MONTH_END' or exclude_calendar='ML_CALENDAR_MONTH_END';
---
Feedback
