Update Report Tables job fails with error "ORA-06512" in RPT_CALENDAR_SP
search cancel

Update Report Tables job fails with error "ORA-06512" in RPT_CALENDAR_SP

book

Article ID: 145897

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

The following error appears when the Update Report Tables job is run:

Couldn't execute SQL: BEGIN
RPT_CALENDAR_SP();
RPT_INV_HIERARCHY_SP();
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "RPT_CALENDAR_SP", line 255
ORA-06512: at line 2

Environment

All Clarity versions

Cause

The weekly time slices do not start on the designated first day of the workweek.

When setting up the DWH time slices from the Administration > Data Administration > Time Slices page, ensure that the start day of the slice ID matches that of the Administration > Project Management > Settings > General > First Day of Work Week

Use the following queries to quickly find out what the days are set to:

select sr.id, sr.request_name, SR.FROM_DATE
from PRJ_BLB_SLICEREQUESTS SR
where SR.IS_DWH_REQUEST = 1
and SR.PERIOD = 1 --weekly
   order by SR.id


select prweekstart from PRSITE

  • 0 - Sunday
  • 1 - Monday
  • 2 - Tuesday
  • 3 - Wednesday
  • 4 - Thursday
  • 5 - Friday
  • 6 - Saturday

Resolution

Reslice the DWH slice request to start on a day consistent with the First Day of Work Week set in Administration ->Settings

  1. Go to Administration > Data Administration > Time Slices page
  2. Modify all the Weekly DWH time slice IDs to conform to the First Day of Work Week day:
  3. Sort all the weekly DWH time slices by ID.
  4. Open each ID link in a new browser tab.
  5. Modify the 'From Date' to an agreed date that has the same day of the week as that listed in Project Management ->Settings

Tip: To check what your First day of Work Week is set to from the UI side:

  1. In Clarity, go to Administration > Project Management > Settings >
  2. View the value set in General > First Day of Work Week