Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 53 Oracle Analytics and AI Sharing Center
- 25 Oracle Analytics and AI Lounge
- 306 Oracle Analytics and AI News
- 57 Oracle Analytics and AI Videos
- 16.4K Oracle Analytics and AI Forums
- 6.6K Oracle Analytics and AI Labs
- Oracle Analytics and AI User Groups
- 118 Oracle Analytics and AI Trainings
- 22 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
ESS Job Display Name Not Available
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.
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 -
Hi @KiranChittoor , Welcome to the Oracle Analytics Community.
Hopefully above query solves your issue. Otherwise, please consider putting your question in
Fusion Applications Administration — Cloud Customer Connect .
Hope it helps!
0 -
Hi Bhaskar,
I don't think it provides the ESS Job Display name as this is not stored. Can you recheck?
Thanks.1 -
Hi Mandeep,
You are correct as always.
Actually after seeing your post, was trying to share the near work around which we used previously.
Thanks for your time & help.
0 -
Hi Bhaskar,
Thanks for the query. However, the query is returning the ESS job name and not the display name.
Thanks.0 -
Hi Kiran,
Thanks for the confirmation.
As Mandeep mentioned, it is not possible to fetch the 'Display Name' as it is not being stored. I was sharing the work around to get the Job Name which we used in our previous project.
Thank you.
0
