Oracle Communities

Oracle Analytics and AI

Child Item

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.

Find more posts tagged with

Accepted answers

All comments

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/28353/ess-job-display-name-not-available

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.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/28353/ess-job-display-name-not-available

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 processstart

Hope this help.

Thank you.

RVohra

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!

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/75540#Comment_75540

Hi Bhaskar,
I don't think it provides the ESS Job Display name as this is not stored. Can you recheck?
Thanks.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/75579#Comment_75579

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.

KiranChittoor

Hi Bhaskar,
Thanks for the query. However, the query is returning the ESS job name and not the display name.
Thanks.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/75617#Comment_75617

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.