Running on its own

Until now someone has had to press a button. Rise and set times ran out after a week, ISS passes after three days, and the calendar after its year. Part 10 makes SkySutra look after itself. Every night it works out the week ahead for every place, and every ten minutes it checks whether anyone should be told the ISS is about to fly over. On Sunday evening it emails the week ahead to everyone who asked for it. And a page shows what ran while nobody was watching.

How Part 10 works

Before you start

You need Part 9 done. Download and run skysutra_jobs.sql in SQL Commands. It adds:

• SS_JOB_RUNS, the job log: one row for each step of each run
• SS_ALERTS_SENT, which remembers what was sent to whom, so nobody gets the same alert twice
• WEEKLY_EMAIL as a third kind in SS_ALERT_PREFS (Part 2 made it with ISS_PASS and AURORA)
• package SS_NIGHTLY, the nightly chain's steps
• package SS_ALERTS, the push notifications and the weekly email
• view SS_JOB_RUNS_V, the log with local start times and how long each step took

Lesson 10.1 — A nightly chain

Outcome: an automation that runs at 3:30 every morning and refreshes everything that runs out.

The question. What goes stale, and how fast?

The ISS passes need fresh orbital elements, and those already arrive on their own: the CelesTrak REST Data Source has synchronized every few hours since Lesson 4.3. So the chain only computes.

There's a fourth job: deleting what's old. Rise and set times older than two weeks, passes that ended two days ago, sent-alert rows after a month, and the log itself after 60 days.

One step, one log row

SS_NIGHTLY.STEP runs one step by name and writes a row in SS_JOB_RUNS before it starts:

procedure step (p_step varchar2, p_job varchar2 default 'NIGHTLY') is
    l_id   number := log_start(p_job, p_step);
    l_rows number := 0;
begin
    case p_step
    when 'RISE_SET' then
        for p in (select id from ss_places order by id) loop
            ss_almanac.refresh(p.id, p_days => 8);
            commit;                       -- one place at a time: a long step makes progress
            l_rows := l_rows + 1;
        end loop;
    ...
    end case;
    log_end(l_id, 'OK', l_rows);
exception
    when others then
        rollback;
        log_end(l_id, 'FAILED', l_rows, sqlerrm);
        raise;
end step;

log_start and log_end are autonomous transactions (pragma autonomous_transaction): each commits its own row and nothing else. That matters when a step fails. The step's rollback undoes its half-done work, but the FAILED row and its error message stay. Then raise passes the error on, so whatever called the step knows it failed.

An automation with four actions

An Automation is APEX's way to run code on a schedule, inside the application. It runs with the application's settings, so apex_mail, apex_pwa and your packages all work as they do on a page. (A plain DBMS_SCHEDULER job would run outside APEX, without that.)

An automation can work through the rows of a query, one action per row. This one doesn't need rows. It runs its actions Always, once each time, in order of sequence:

Each action has Stop Execution On Error set. If the ISS step fails, the calendar and tidy steps don't run, and the automation's own log says so.

Why four actions, not one call to run_all? The automation log then shows each step separately. And you can turn one step off, or add another between two, without touching the package.

When it runs

An automation's schedule is in UTC, written the way DBMS_SCHEDULER writes them:

FREQ=DAILY;INTERVAL=1;BYHOUR=7;BYMINUTE=30

07:30 UTC is 3:30 in the morning in Florida in summer time, and 2:30 after the clocks go back on Nov 1. Any time between midnight and dawn works: the job only needs to be done before anyone opens the app in the morning.

Build: the Nightly sky automation

1. Shared Components › Automations › Create:
   • Name Nightly sky, Type Scheduled, Actions Initiated On Always.
   • Execution Schedule Custom: FREQ=DAILY;INTERVAL=1;BYHOUR=7;BYMINUTE=30.
   • Action: Execute Code, ss_nightly.step('RISE_SET');.
2. Rename the first action Rise and set times, and set Stop Execution On Error on.
3. Add Action three times for the ISS, Calendar and Tidy-up steps, with sequences 20, 30 and 40.
4. Set the automation's Static ID to nightly-sky, Schedule Status to Active, then Save and Run.

The first run takes about three and a half minutes. Watch the steps arrive:

select step, status, started, seconds, rows_done, message
  from ss_job_runs_v
 order by id desc;

On the first test run: RISE_SET 170 s for nine places, ISS 0.6 s, CALENDAR 23 s, TIDY 0 s.

If Next Run stays empty. Sometimes an automation saved as Active shows no next run, and it never starts. Turn it off and on again from SQL, which makes APEX schedule it:

begin
    apex_automation.disable(p_application_id => 199978, p_static_id => 'nightly-sky');
    apex_automation.enable (p_application_id => 199978, p_static_id => 'nightly-sky');
end;

Lesson 10.2 — Work in the background

Outcome: a Jobs page where an administrator can run the whole chain now, and watch it go.

In Lesson 7.1 a background execution chain worked out a new place's sky after the page had answered. Here a whole night's work runs the same way, and the page shows how far it has got.

Progress

A procedure running in a background chain can report how far it has got:

procedure run_all (p_job varchar2 default 'NIGHTLY') is
begin
    for i in 1 .. c_steps.count loop
        begin
            apex_background_process.set_progress(p_totalwork => c_steps.count, p_sofar => i - 1);
        exception when others then null;     -- not in a background chain: nothing to report
        end;
        step(c_steps(i), p_job);
    end loop;
    ...
end run_all;

APEX keeps the numbers, and the view APEX_APPL_PAGE_BG_PROC_STATUS shows them for each execution: its status, sofar of totalwork, a status message, and when it was created.

Only one at a time

Two copies of the chain at once would both delete and refill the same rows. The execution chain's Serialize setting stops that: a second Run Now waits in the queue until the first has finished.

Build: the Jobs page

Create Page › Blank Page: 80, Jobs, with Authorization Scheme Administration Rights. It goes in the navigation menu, under the same scheme.

1. Run it now (Static Content): one line explaining what the button does, and a button RUN, "Run Now".

2. A process Run the nightly chain:

   • Type Execution Chain, Run in Background on, Serialize on, when RUN is pressed.
   • Success Message: "Started in the background. Watch it below."
   • Its one child process, All four steps: ss_nightly.run_all(p_job => 'MANUAL');.

3. Background work (Classic Report, HTML DOM ID background-work):

   select process_name, status,
          case when totalwork is not null then sofar || ' of ' || totalwork end as progress,
          status_message, to_char(created_on, 'Dy DD Mon HH24:MI:SS') as started
     from apex_appl_page_bg_proc_status
    where application_id = :APP_ID
    order by created_on desc
    fetch first 5 rows only
   

   It lists every background execution in the app, so a place saved in Lesson 7.1 shows here too. Those don't report progress, which is why the case: without it they would show a lonely " of ".

4. Recent runs (Classic Report on SS_JOB_RUNS_V, HTML DOM ID recent-runs), newest first. The job column tells the nightly automation (NIGHTLY), Run Now (MANUAL) and the alerts (ALERTS, WEEKLY_EMAIL) apart.

5. Page › Execute when Page Loads: refresh both reports every five seconds.

   setInterval(function () {
       apex.region('background-work').refresh();
       apex.region('recent-runs').refresh();
   }, 5000);
   

   Static ID or HTML DOM ID? A region has both. apex.region('recent-runs') finds a region by its HTML DOM ID, the id on the page. Static ID names the region for APEX itself. If only the Static ID is set, apex.region() returns null and the script stops with "Cannot read properties of null (reading 'refresh')".

Press Run Now. The page answers at once. "0 of 4" becomes "1 of 4" after about three minutes, and the last three steps follow within half a minute.

An automation or a page process? The automation runs by itself on a schedule, and nobody needs to be signed in. The background chain runs because someone pressed a button, in their session. Both call the same package, so there's one copy of the work.

Lesson 10.3 — Push notifications

Outcome: "ISS over Wesley Chapel in 20 minutes" arrives on your phone or laptop, even with SkySutra closed.

How web push works

1. The browser asks permission. If you say yes, it gives the app a subscription: an address at the browser maker's push service (Google's for Chrome, Apple's for Safari, Mozilla's for Firefox).
2. APEX stores that subscription against your user name.
3. To notify you, APEX signs a message with the app's own key pair and posts it to that address. The push service delivers it to your device, and the browser shows it, even when no SkySutra tab is open.

APEX handles all of it. Shared Components › Progressive Web App › Enable Push Notifications creates the key pair (a credential, app-199978-push-notifications-credentials). You don't need to make the app installable for push to work.

Subscribing

The Create Page › Feature › Push Notifications wizard adds two pages:

• Settings (page 20000), opened from the user menu in the navigation bar, listing the User Settings list
• Push Notifications (page 20010), where a person turns push on or off for this browser

A subscription belongs to one browser on one device. Subscribing on your laptop doesn't subscribe your phone.

Who wants what

SS_ALERT_PREFS has been waiting since Part 2. The new Sky Alerts page (81, a modal dialog, added to the User Settings list) sets it:

• ISS passes (yes or no), and Only passes higher than 10°, 30°, 45° or 60°. A pass that stays at 12° is low in the haze; 45° and up is worth going out for.
• Aurora (yes or no)
• The week ahead by email (yes or no)
• A display-only line saying whether this person has a push subscription yet, from apex_pwa.has_push_subscription. Without one, push alerts can't reach them.

Save calls ss_alerts.save_prefs, which merges one row per kind for ss_auth.current_user_id.

Deciding whom to tell

ss_alerts.send_due_pushes runs every ten minutes. For each person with ISS alerts on, it finds visible passes over their home, high enough for them, that start 15 to 25 minutes from now. A ten-minute window, checked every ten minutes, catches every pass once. Twenty minutes is long enough to put shoes on and get outside.

if apex_pwa.has_push_subscription(p_user_name => upper(r.email))
   and first_time(r.user_id, 'ISS_PASS', to_char(r.pass_id)) then
    apex_pwa.send_push_notification(
        p_user_name  => upper(r.email),
        p_title      => 'ISS over ' || r.place || ' in ' || l_minutes || ' minutes',
        p_body       => 'It rises in the ' || compass(r.start_azimuth_deg) || ' at ' || ...,
        p_target_url => apex_page.get_url(p_page => 42));

• User names are upper case. APEX stores them that way, so the email address is passed through upper.
• first_time inserts into SS_ALERTS_SENT, whose primary key is (user, kind, reference). If the row is already there, the insert fails with dup_val_on_index, and the function returns false. So a pass is never announced twice, even if the job runs twice in the same window.
• The target URL opens the ISS page (42) when the notification is tapped.

Aurora works the same way. If ss_live.aurora_outlook (Lesson 4.4) starts with "Aurora possible", each person gets at most one aurora alert in each half-day: the reference is the UTC date plus AM or PM.

Build: the Sky alerts automation

Shared Components › Automations › Create: Sky alerts, static ID sky-alerts, Scheduled, Always, FREQ=MINUTELY;INTERVAL=10, one action ss_alerts.send_due_pushes;.

There's a declarative way too. An automation can query rows (for example, "passes starting in 15–25 minutes, with the person to tell") and run a Send Push Notification or Send E-Mail action on each row, with &COLUMN. substitutions in the title and body. That's a good choice for a single, simple alert. SkySutra uses one procedure because it needs SS_ALERTS_SENT, two kinds of alert, and its own log row.

Lesson 10.4 — A weekly sky email

Outcome: on Sunday evening, everyone who asked gets an email with the coming week's sky over their home.

An email template

Shared Components › Email Templates › Create: The week ahead, static ID WEEK_AHEAD.

• Subject: The week ahead over #PLACE#

• Header: SkySutra's name in its blue.

• Body:

  <p>Hello #NAME#,</p>
  <p>Here's the sky over #PLACE# for #WEEK#.</p>
  <h3>Events</h3>
  #EVENTS!RAW#
  <h3>ISS passes you can see</h3>
  #PASSES!RAW#
  

• Footer: why they're getting it, and that they can turn it off in Settings › Sky Alerts.

• Plain text: the same words, without the tables, for mail programs that don't show HTML.

#NAME# is escaped, so a name with < in it shows as text. #EVENTS!RAW# is not escaped: it's HTML the package built, and every value inside it was already escaped there with apex_escape.html.

Filling it in

ss_alerts.week_events_html lists the week's events from SS_SKY_EVENTS, the Moon's phases, and four tithis worth knowing (Ekadashi, Purnima, the second Ekadashi and Amavasya), in the person's home time zone. week_passes_html lists the visible ISS passes. Email programs ignore most stylesheets, so the HTML uses inline styles.

The placeholders go to apex_mail.send as one JSON object:

function placeholders (p_user_id number, p_name varchar2, p_place varchar2) return clob is
    l_json json_object_t := json_object_t();
begin
    l_json.put('NAME',   p_name);
    l_json.put('PLACE',  p_place);
    l_json.put('WEEK',   to_char(sysdate, 'DD Mon') || ' to ' || to_char(sysdate + 6, 'DD Mon YYYY'));
    l_json.put('EVENTS', week_events_html(p_user_id));
    l_json.put('PASSES', week_passes_html(p_user_id));
    return l_json.to_clob;
end;
...
apex_mail.send(
    p_to                 => r.email,
    p_from               => 'you@example.com',
    p_template_static_id => 'WEEK_AHEAD',
    p_placeholders       => placeholders(r.id, r.display_name, r.place));
...
apex_mail.push_queue;

apex_mail.send only puts the email in a queue. APEX sends the queue every few minutes; push_queue sends it now.

Time zones in SQL. at time zone l_tz with a PL/SQL variable fails inside a query (ORA-00923). Bring the zone in as a column instead: with home as (select p.time_zone as tz from ...) cross-joined, then e.peaks_at at time zone home.tz.

Build: the Weekly sky email automation

Shared Components › Automations › Create: Weekly sky email, static ID weekly-sky-email, Scheduled, Always, FREQ=WEEKLY;BYDAY=SUN;BYHOUR=21;BYMINUTE=0 (5 pm on Sunday in Florida in summer, 4 pm in winter), one action ss_alerts.send_weekly_email;.

To try it without waiting for Sunday, the Sky Alerts page has an Email Me This Week Now button. It calls ss_alerts.send_weekly_email(p_only_user_id => ss_auth.current_user_id), which sends to you alone.

The From address. Mail services check that the sender is allowed to send for its domain. An email "from" a Gmail address that was really sent by Oracle's servers may go to spam, or be refused. On apex.oracle.com, use an address you own; in your own instance, use one your mail server is allowed to send for.

Checkpoint

SkySutra now keeps itself up to date every night, tells people when the ISS is about to pass, and emails them the week ahead.

Test it

1. SQL Commands: select step, status, seconds, rows_done from ss_job_runs_v where job = 'NIGHTLY' order by id desc shows last night's four steps, all OK. select max(local_date) from ss_rise_set is a week from today.
2. Jobs: press Run Now. The page answers at once; Background work goes from 0 of 4 to 4 of 4 in about three and a half minutes, and Recent runs fills with MANUAL rows.
3. Settings › Push Notifications: subscribe in this browser and allow notifications.
4. Settings › Sky Alerts: the push line now says you're subscribed. Turn on ISS passes at 10° and aurora, and save. The next visible pass over your home (on the ISS page) arrives as a notification about 20 minutes before it starts.
5. Email Me This Week Now: the email arrives with the week's events and passes, in your home time zone. Check the spam folder, and see the note about the From address.
6. Jobs › Recent runs shows an ALERTS row every ten minutes and your WEEKLY_EMAIL row.

Files

Exercises

• Add a fifth nightly step that emails the administrators when any step of last night's run FAILED. Use apex_mail.send with a second template.
• Let people choose the alert's lead time (10, 20 or 30 minutes) on the Sky Alerts page. You'll need a column in SS_ALERT_PREFS and a wider window in send_due_pushes.
• Rewrite the aurora alert as a declarative automation: a query that returns the people to tell, and a Send Push Notification action. Compare it with the PL/SQL version.
• Put a "Last refreshed" line on the Tonight page from SS_JOB_RUNS, saying when the nightly chain last finished OK.
• Stretch: send the weekly email at 5 pm local time for each person, wherever they live. Run the automation every hour and pick the people whose local time is Sunday 17:00.

Review questions

Try each one before you open it. The graded quiz is at the end of the page.