Scheduled Job APEX Dashboard

Posted in Schedules & Alerts.

There have been cases where clients have complex schemas with tables that are used for reporting, that need to be refreshed often to update the reporting data.

Rather than having to manage this via scripts manually, you can create dashboards that manage and monitor a number of Scheduler Jobs in the database.For example, I have created an application with a page for the monitoring of each job.(This is linked to a homepage where you can see the status of all jobs):DBMS_Scheduler_dash_1_v

Page Layout:

This is the behind the scenes layout of the page:



Scheduled Job Details:

The top region, "Scheduled Job Details" shows the details of the scheduled job.

select job_name,
CASE when state = 'SCHEDULED'THEN '' || state || ''ELSE '' || state || ''END STATE, 
CASE when state = 'SCHEDULED'THEN  next_run_date ELSE  null END next_run_date,
sysdate current_time
from user_scheduler_jobs
where job_name = 'UPDATE_TABLE_ABC_JOB'

To make the report display down the page instead of across:
Report Attributes > Report Template = "Value Attribute Pairs" .

Refresh Log:

The second region, the "Refresh Log" shows the log of each time the job was run.

select log_date, job_name, status, actual_start_date, run_duration, additional_info
from user_scheduler_job_run_details
where job_name = 'UPDATE_TABLE_ABC_JOB'

order by log_date desc


Run Immediate Button:

The Run Immediate button allows the user to run the job ad-hoc.

In the page button properties, the Optional URL Redirect section is set as:

Target is a = "URL"

URL Target = javascript:{if (confirm('Are you sure that you want to run this job now?')) doSubmit('RUN');}

This makes a popup appear for the user to confirm before they run the job.

This then runs a page process, that is conditionally run when this button is pressed.
The process that runs is:

dbms_scheduler.run_job('UPDATE_TABLE_ABC_JOB', false);


Enable & Disable Buttons:Each button is linked to a page process. Each page process runs conditionally when either of the buttons is pressed.

The processes are:








Modify Schedule Button:

This button redirects to another page, passing the job name and the page number.

The 'modify schedule' process has been created on a separate page, so that the same page can be re-used for any job you like.

A branch has been created that is conditionally based on whether the "Modify" button is pressed.



Modify Schedule Page:

The "Modify Schedule" page looks like this:

The Run the Job field (select list with submit) has the options of: Daily, Weekly, Monthly. The following fields are then displayed conditionally depending on what is chosen.


There is also a hidden item called P27_PAGE which stores the page that called this page, so that we can redirect back there when we have finished.

See the page backend below.


The page processes are called when the "Set Schedule" button has been pressed, but they are all conditional depending on what is chosen in the P27_DAILYWEEKLY field (Daily, Weekly or Monthly).

Set Schedule DAILY:

DBMS_SCHEDULER.set_attribute (
name      => :P27_JOB_NAME,
attribute => 'repeat_interval',
value     => 'freq=daily; byhour='||:P27_HOUR);

Set Schedule WEEKLY:

DBMS_SCHEDULER.set_attribute (
name      => :P27_JOB_NAME,
attribute => 'repeat_interval',
value     => 'freq=weekly; byday='||:P27_DAY||'; byhour='||:P27_HOUR);

Set Schedule MONTHLY:

DBMS_SCHEDULER.set_attribute (
name      => :P27_JOB_NAME,
attribute => 'repeat_interval',
value     => 'freq=monthly; bymonthday='||:P27_MONTHDAY||'; byhour='||:P27_HOUR);




Select All / Deselect All

Posted in Fields & Items.

(written for Apex v 3.x)

To make it easy for you application users, there may sometimes be the need for a "Select All" and "Deselect All" functionality for your checkbox group.

In this example, I have used icon buttons for the functions, as seen in the image below.


1) I uploaded the images I want to user for my buttons into the shared components of my application. These images are called:

2) I have then created a checkbox item on my page called P1_BUSINESS_GROUP.

3) I have placed this checkbox within its own Page Region, the title of this region being "Business Group", which is what is displayed in the image.

4) Place this javascript function into the HTML Header of your page definition:

<script type="text/javascript"></span><br /><span class="Moobi_Code">function get_Checkboxes(p_itemname)
 var ip=document.getElementsByTagName('INPUT');
 var ret=new Array();
 var regexp=new RegExp('^'+p_itemname+'_');
 for (var j=0;j<ip.length;j++) {
   if (ip[j].type=="checkbox"
   && ip[j].id && ip[j].id.match(regexp))
 return ret;

4) In the LABEL field of my P1_BUSINESS_GROUP item, I have placed the following code:

<a href="#"  onClick="html_CheckAll(null,true,get_Checkboxes ('P36_BUSINESS_GROUP'))"> <img src="#WORKSPACE_IMAGES#select_all.gif"   valign="bottom"  title="Select All"></a> &nbsp; <a href="#"  onClick="html_CheckAll(null,false,get_Checkboxes ('P36_BUSINESS_GROUP'))"> <img src="#WORKSPACE_IMAGES#deselect_all.gif"  valign="bottom"  title="Unselect All"></a></span><br /><br /><br /><img src="images/stories/APEX/Items/select_deselect_all_fieldcode.jpg" alt="select_deselect_all_fieldcode" width="634" height="135" />

<script type="text/javascript"><br />function get_Checkboxes(p_itemname)<br />{<br /> var ip=document.getElementsByTagName('INPUT');<br /> var ret=new Array();<br /> var regexp=new RegExp('^'+p_itemname+'_');<br /> for (var j=0;j<ip.length;j++) {<br />   if (ip[j].type=="checkbox"
   && ip[j].id && ip[j].id.match(regexp))
 return ret;

Calling HTMLDB_MAIL.SEND procedure from within a Package/Job

Posted in General Application.

The HTMLDB_MAIL.SEND must be called from within an application session. This means that you can call it from a script within an APEX workspace, from a piece of code witin a page, or within a Produre that IS CALLED from the application.

Sometimes you may want to send mail from a package/procedure within your workspace shema, but the procedure may be called elsewhere, for example a DBMS Scheduled Job.

If you try this, you will get an error along the lines of:
"This procedure must be invoked from within an application session"

You can get around this by executing the following code before your call to the HTMLDB_MAIL.SEND procedure


This code sets the variable that contains the workspace ID to the workspace ID of the workspace to which the current schema is assigned.
After this package variable is set, the mail package is then satisfied that an authorized caller invoked it.

This will allow the apex_mail.send procedure to be called from any schema that is mapped to a workspace.

About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2