Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 53 Oracle Analytics and AI Sharing Center
- 19 Oracle Analytics and AI Lounge
- 289 Oracle Analytics and AI News
- 57 Oracle Analytics and AI Videos
- 16.3K Oracle Analytics and AI Forums
- 6.5K Oracle Analytics and AI Labs
- Oracle Analytics and AI User Groups
- 111 Oracle Analytics and AI Trainings
- 21 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture
ESS Job Display Name Not Available
KiranChittoor
Rank 1 - Community Starter
Hi Team,
We are working on developing a report to identify all scheduled ESS jobs. Currently, we are able to retrieve only the short name of the ESS job from the available tables, but the full display name is not being returned.
Is there any table or view where the display name of the ESS job is stored?
We also reviewed the ESS_REQUEST_HISTORY table, but observed that for some scheduled jobs the job name is not populated.
Please let us know if there is a recommended query or table that can be used to retrieve the full ESS job display name.
Thanks,
Kiran.
0
Answers
-
Hi Kiran,
Unfortunately, this is not available yet as these details are stored as METADATA not in direct tables.
What are the Tables Where the ESS Jobs and Custom Job Sets are Stored? (Doc ID 2430380.1)There is an idea already submitted for the same. Please evaluate and upvote:
Required ESS Schedule Process Job Name — Cloud Customer Connect (oracle.com)
Thanks.
0 -
Hi @KiranChittoor,
Welcome to the Oracle Analytics & AI Community!
Can you please try the following Query?
SELECT
final.REQUESTID,
final.NAME,
final.PROCESSSTART,
final.PROCESSEND,
final.NUMBEROFMINUTESTORUN,
final.SUBMITTER,
final.REQUEST_STATE,
final.PARENT_CHILD,
final.PARENTID ,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 1) param1,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 2) param2,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 3) param3,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 4) param4,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 5) param5,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 6) param6,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 7) param7,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 8) param8,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 9) param9,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 10) param10,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1,11) param11,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 12) param12,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 13) param13,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 14) param14,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 15) param15,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 16) param16,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 17) param17,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 18) param18,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 19) param19,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 20) param20,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 21) param21,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 22) param22,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 23) param23,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 24) param24,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 25) param25,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 26) param26,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 27) param27,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 28) param28,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 29) param29,
REGEXP_SUBSTR(final.parameters, '[^'||CHR(10)||']+', 1, 30) param30
FROM (
SELECT requestid
,nvl(name, substr(DEFINITION, instr(DEFINITION, '/', - 1) + 1)) name
,to_char(processstart, 'DD-MM-YY HH24:MI:SS', 'NLS_DATE_LANGUAGE=AMERICAN') processstart
,to_char(processend, 'DD-MM-YY HH24:MI:SS', 'NLS_DATE_LANGUAGE=AMERICAN') processend
,round((extract(DAY FROM nvl(processend, systimestamp) - processstart) * 24 * 60 * 60 * 1000 + extract(HOUR FROM nvl(processend, systimestamp) - processstart) * 60 * 60 * 1000 + extract(MINUTE FROM nvl(processend, systimestamp) - processstart) * 60 * 1000 + extract(SECOND FROM nvl(processend, systimestamp) - processstart) * 1000) / 60000, 2) NumberofMinutesToRun
,SUBMITTER
,DECODE(STATE, 1, 'Wait', 11, 'Warning', 2, 'Ready', 12, 'Succeeded', 3, 'Running', 13, 'Paused', 4, 'Completed', 14, 'Pending Validation', 5, 'Blocked', 15, 'Validation Failed', 6, 'Hold', 16, 'Schedule Ended', 7, 'Canceling', 17, 'Finished', 8, 'Expired', 18, 'Error Auto-Retry', 9, 'Canceled', 19, 'Error Manual Recovery', 10, 'Error', STATE) request_state
,decode(ABSPARENTID, REQUESTID, 'Parent', 'Child') Parent_Child
,absparentid ParentID
,(
SELECT LISTAGG(Parameters, CHR(10)) WITHIN
GROUP (
ORDER BY lel
)
FROM (
SELECT NVL((
SELECT substr(value, instr(value, '.', 101) + 1, instr(value, '.', 101, 2) - 1 - instr(value, '.', 101)) || '(' || 'argument' || lel || ')'
FROM ess_request_property
WHERE requestid = erh.requestid
AND name = 'display.attribute' || lel || '.label'
AND rownum = 1
), (
SELECT 'argument' || lel value
FROM ess_request_property
WHERE requestid = erh.requestid
AND name = 'submit.argument' || lel
AND rownum = 1
)) || ' →' || nvl((
SELECT value len
FROM ess_request_property
WHERE requestid = erh.requestid
AND name = 'display.attribute' || lel || '.value'
AND rownum = 1
), (
SELECT value
FROM ESS_REQUEST_PROPERTY
WHERE requestid = erh.requestid
AND name = 'submit.argument' || lel
AND rownum = 1
)) Parameters
,lel
FROM (
SELECT LEVEL lel
FROM dual connect BY LEVEL <= 50
)
)
WHERE Parameters <> ' →'
) parameters
FROM ESS_REQUEST_HISTORY erh
) final
WHERE 1 = 1
ORDER BY processstartHope this help.
Thank you.
0
