CREATE OR REPLACE Function RW_SERVER.RS_JOB_CHECK
 ( QueueState IN varchar2 )
 RETURN varchar2
IS
/*
Checks Reports Server job queue an generates an email alert if the number 
of jobs for a given state exceeds 50 e.g. "Current" jobs.

This assumes that the RW_SERVER_JOB_QUEUE table and PL/SQL API objects already exist,
and that the "Job Status Repository" has been configured and enabled within the Reports
Server Instance being checked.

Valid JOB_QUEUE states are CURRENT, PAST, or SCHEDULED.

The function could easily be adapted to check against the STATUS_CODE values:
1 (ENQUEUED), 2 (OPENING), 3 (RUNNING), 4 (FINISHED), 5 (TERMINATED_W_ERR) etc.

Full list of status codes:
https://docs.oracle.com/cd/E12839_01/bi.1111/b32121/pbr_tune003.htm#CHDIHFEC

Version: 1.0

Modifications:
29/01/2016 GH Procedure created.
*/

JobCount number(6);
 IncLvl varchar2(30);

cursor c1 is
 SELECT COUNT(*) JobCount
 FROM RW_SERVER_JOB_QUEUE
 WHERE UPPER(JOB_QUEUE) = QueueState; --CURRENT, PAST, or SCHEDULED
 --AND JOB_TYPE != 'Scheduled';

BEGIN

open c1;
 fetch c1 into JobCount;
 close c1;

IF JobCount <= 0 THEN
 IncLvl := 'Nothing to do';

ELSIF JobCount >= 1 and JobCount <= 49 THEN
 IncLvl := 'Working through reports';

ELSE
 IncLvl := 'Potential problem';
 send_mail(p_to => 'garth@snapdba.com',
 p_from => 'noreply@example.com',
 p_subject => 'Alert! Reports Server Instance on SERVER1',
 p_message => 'The number of "Current Jobs" is '||JobCount||'.

This is an indication that the Report Server Instance (SERVER1) may have stalled processing.

Please contact the On Call DBA.' ,
 p_smtp_host => 'smtp.example.com');

END IF;
RETURN IncLvl;
END;
/