Generating Report on ESS Jobs with End Dates
Summary:
We are currently working on generating a report to identify the ESS (Enterprise Scheduler Service) jobs that are scheduled with an end date. Specifically, we would like to extract the following details:
- Process ID
- Job Name
- Status
- Scheduled Time
- Submission Time
- Submitted By
- Metadata Name
- Frequency of Run
We have a script from Oracle that provides us some information, but we need assistance in modifying it to include the job name and job set name.
SELECT
requestid,
DECODE ( state,1, 'Scheduled',10, 'ERROR',11, 'WARNING',12, 'SUCCEEDED',6, 'Hold',state) STATE,
SUBSTR (definition, (INSTR (definition, '/', - 1) + 1)) Name ,
TO_TIMESTAMP(SUBSTR((SUBSTR(REPLACE(utl_raw.cast_to_varchar2(adhocschedule),chr(0),''),INSTR(REPLACE(utl_raw.cast_to_varchar2(adhocschedule),chr(0),''),'<start>') +LENGTH('<start>'),INSTR(REPLACE(utl_raw.cast_to_varchar2(adhocschedule),chr(0),''),'</start>') - INSTR(REPLACE(utl_raw.cast_to_varchar2(adhocschedule),chr(0),''),'<start>') -LENGTH('<start>'))),1,19), 'YYYY-MM-DD HH24:MI:SS') SCHEDULE_START,