Export/Query Batch Scheduler details


Someone asked this question in OTN and the generic answer was not it is not available.
I knew that we could generate it. I had a reason to believe so – it shows up in Workspace. Right click on the batch and look at Show Details – there it is.

However it took me a while to get this done. I tried to explore something new 😉 (I was looking to add a stylesheet to XML file and generate the output)

The details are captured in an XML, (oh forgot to mention that Batch Scheduler adds an entry to a table called FR_SCHEDULER_DATA)







and stored in the XML column. The question was how to return those values.

I looked at the XML file (by editing it in SQL Developer) and found that it has the start time and end time (which proves my theory).

<jobstatusmessage INDEX="0" KEY="4106" LF="true">
<parameters PARAMCOUNT="2">
<parameter PARAMETERINDEX="0" PARAMTYPE="String" PARAMVALUE="&quot;My Sample Reports/Batches/Management Pack 1&quot;"/>
<parameter PARAMETERINDEX="1" PARAMTYPE="Date" PARAMVALUE="1402955727248"/>
</PARAMETERS>
</JOBSTATUSMESSAGE>

PARAMVALUE is in Epoch Date.

Since it is an XML file, I was thinking about using EXtensible Stylesheet Language. Here is what I got after creating the XSL file.





It looks great , however not that usable, because you’ll have to extract the XML from table, create a file for each job – it is not easy.

I’m not a good SQL programmer and it took me a while (google ;)) to get a code which can achieve what is needed.

SQL

The below given SQL will extract a scheduled batch’s name, it’s status, start time and end time.

select 
 EXTRACTVALUE(XMLTYPE(XML,1),'/BATCH_JOB_OBJECT/@BATCH_JOB_NAME') as Name,
 CASE EXTRACTVALUE(XMLTYPE(XML,1),'/BATCH_JOB_OBJECT/JOB_STATUS/@CURRENT_STATUS')
 WHEN '2' THEN 'Successful'
 WHEN '3' THEN 'Error'
 WHEN '4' THEN 'Running'
 END as Status,
 timestamp '1970-01-01 00:00:00' + numtodsinterval(extractValue(value(x),'//PARAMETER/@PARAMVALUE')/1000,'second') as Time
 from FR_SCHEDULER_DATA, 
 table(xmlsequence(extract(xmltype(XML,1),'//PARAMETER[@PARAMTYPE="Date"]'))) x;

Here is what it looks like











If you see two timings reported against a batch 1st is the start time and 2nd is the end time.

Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.


Leave a Reply

0 thoughts on “Export/Query Batch Scheduler details