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=""My Sample Reports/Batches/Management Pack 1""/> <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.
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.