CODEWITHSUNDEEP
sqloracle-databasefunctionstored-procedures
Macrus
Macrus

Reputation: 69

Oracle stored procedure with function ORA-08103

I have a stored procedure that has a function call, and I get the below errors after about 10 mins of it running:

[Error] Execution (1: 1):
ORA-08103: object no longer exists
ORA-06512: at "CRYSTAL_REPORTS.FT_PAYCOM_ASOF", line 141
ORA-06512: at "CRYSTAL_REPORTS.PROC_DASHQS_PRODUCTION", line 26
ORA-06512: at line 2

However the function does exist and works as expected. Stripping down the query returns results, so I am concerned that complexity may be the cause. I appreciate any help, below is the procedure:

select
    to_char(dt.dt,'YYYYMM') yearmonth,
    ud.user_id, 
    CRYSTAL_REPORTS.FT_PAYCOM_ASOF(ud.user_eecode, dt.dt, 'DEPT') department,
    CRYSTAL_REPORTS.FT_PAYCOM_ASOF(ud.user_eecode, dt.dt, 'PDEPT') par_department,
    sum(case when clm.event_desc = 'NEW-OPEN' then 1 else 0 end) new_claim,
    sum(case when clm.event_desc in ('INITIAL-CLOSE', 'RECLOSE', 'VOID') then 1 else 0 end) close_claim,
    sum(case when clm.event_desc ='REOPEN' then 1 else 0 end) reopen_claim,
    sum(case when clm.event_desc ='TRANSFER-IN' then 1 else 0 end) trans_in_claim,
    sum(case when clm.event_desc ='TRANSFER-OUT' then 1 else 0 end) trans_out_claim,
    sum(case when res.event_desc ='NEW-OPEN' then 1 else 0 end) new_res,
    sum(case when res.event_desc  in ('INITIAL-CLOSE','RECLOSE','VOID') then 1 else 0 end) close_res,
    sum(case when res.event_desc ='REOPEN' then 1 else 0 end) reopen_res,
    sum(case when res.event_desc ='TRANSFER-IN' then 1 else 0 end) trans_in_res,
    sum(case when res.event_desc ='TRANSFER-OUT' then 1 else 0 end) trans_out_res, 
    sum(clm_wh.pending) pending_claims,
    sum(res_wh.pending) pending_reserves
from 
    (select "DATE" dt from CRYSTAL_REPORTS.MV_CALENDAR_MONTHDATE) dt
cross join 
    crystal_reports.user_director ud
left join 
    CRYSTAL_REPORTS.MV_PROD_CLM_EVENT clm on clm.USER_ID = ud.USER_ID and to_char(clm.event_date,'YYYYMM') = to_char(dt.dt,'YYYYMM')
left join 
    CRYSTAL_REPORTS.MV_PROD_RES_EVENT res on res.USER_ID = ud.USER_ID and to_char(res.event_date,'YYYYMM')=to_char(dt.dt,'YYYYMM')
left join 
    crystal_reports.TBL_CLAIM_PROD_WH clm_wh on clm_wh.ADJUSTER=ud.user_id and clm_wh.type='MONTH' and to_char(dt.dt,'YYYYMM')= clm_wh.datadate
left join 
    crystal_reports.TBL_FEAT_PROD_WH res_wh on res_wh.ADJUSTER=ud.user_id and res_wh.type='MONTH' and to_char(dt.dt,'YYYYMM')= res_wh.datadate
where
    to_char(dt.dt,'YYYYMMDD') = 20210901
    and ud.user_id not like '%TEST%'
group by
    to_char(dt.dt,'YYYYMM'), ud.user_id,
    CRYSTAL_REPORTS.FT_PAYCOM_ASOF(ud.user_eecode, dt.dt, 'DEPT'), 
    CRYSTAL_REPORTS.FT_PAYCOM_ASOF(ud.user_eecode, dt.dt, 'PDEPT')

The function goes through several IF statements, and ends up using:

       SELECT upper(case when uc_dept.detaildesc is null and orig_dept.detaildesc is null then upper(pext_dept.detaildesc) else upper(nvl(uc_dept.detaildesc,orig_dept.detaildesc)) end)
       INTO xout_val
       FROM crystal_reports.API_PAYCOM_USER_EXTENDED pext 
       left join crystal_reports.API_PAYCOM_USER_CHANGES uc on pext.EECODE = uc.EECODE and changedesc='PAF: Department Change' and (to_date(substr(changetime, 1,10), 'yyyy-mm-dd')) <= asof
       left join crystal_reports.api_paycom_category pext_dept on pext_dept.detailcode=pext.DEPARTMENT_CODE
       left join crystal_reports.api_paycom_category uc_dept on uc_dept.DETAILCODE=uc.new_value
       left join (select eecode, orig_value,rn 
                   from
                    (
                    select eecode,old_value orig_value, row_number() over (partition by eecode order by (to_date(substr(changetime, 1,10), 'yyyy-mm-dd')) asc) rn
                    from
                    crystal_reports.API_PAYCOM_USER_CHANGES orig_val
                    where changedesc='PAF: Department Change' 
                    ) 
                 ) orig_val on pext.eecode=orig_val.eecode and  orig_val.rn=1
       left join crystal_reports.api_paycom_category orig_dept on orig_dept.detailcode=orig_val.orig_value             
       where
       acct=pext.eecode
       order by (to_date(substr(changetime, 1,10), 'yyyy-mm-dd')) desc
       FETCH NEXT 1 ROWS ONLY

Upvotes: 0

Views: 430

Answers (1)

doberkofler
doberkofler

Reputation: 10351

It is very hard to remote diagnose such an error but in my experience the most likely cause of this error is another process/user that has removed the object since the operation has begun. Alternatively there are also some Oracle bugs related to this error and you might want to check your alert log and eventually ask Oracle for help using MOS.

Upvotes: 1

Related Questions