Live data

Some of the sky can't be computed: the ISS's orbit changes every time it fires its engines, the Sun's storms are measured, not predicted, and a picture of the day is chosen by people. Six lessons that bring all three in from web services, on a schedule, and keep the app honest when a service goes quiet. You finish with three REST Data Sources synchronizing themselves and a Live card on the home page that says how fresh each answer is.

How Part 4 works

All three services are free. Only NASA wants a key, and it offers a public demo key to start with.

Before you start

You need Part 3 done. Download this part's files:

Run two of them now. In SQL Workshop › SQL Scripts, upload and run skysutra_iss.sql (two objects, SS_ISS and its body, both valid), then skysutra_iss_tests.sql. Its comments at the end show the results to expect: positions within a fifth of a metre of the reference, and a list of passes over Wesley Chapel. skysutra_live.sql waits for Lesson 4.4.

Lesson 4.1 — REST Data Sources

Outcome: describe a web service once, as a shared component, and know when that beats a call in PL/SQL.

The question. SS_ISS.load_elements already fetches the ISS's orbit with apex_web_service.make_rest_request and 30 lines of json_table. Why learn another way?

The decision. Use a REST Data Source when APEX components will read the data or when it should be copied on a schedule; keep apex_web_service for one-off calls inside PL/SQL logic.

NOAA's Kp index

The Kp index measures how disturbed the Earth's magnetic field is, from 0 (calm) to 9 (extreme storm), every three hours. High Kp means aurora further from the poles. NOAA's Space Weather Prediction Center publishes the last week as JSON:

https://services.swpc.noaa.gov/products/noaa-planetary-k-index.json

1. Shared Components › REST Data Sources › Create, From scratch, Next.
2. REST Data Source Type Simple HTTP; Name NOAA Kp index; URL Endpoint the URL above. Next.
3. Remote Server: keep Create New. APEX splits the URL into a base (https://services.swpc.noaa.gov/) and a path. A remote server is shared: every data source on that host uses it, so moving to a test server later is one change. Next.
4. Pagination Type No Pagination. Next.
5. Authentication Required off. Discover.

APEX calls the URL, reads the JSON, and proposes a data profile: four columns, KP, TIME_TAG, A_RUNNING, STATION_COUNT, with TIME_TAG a DATE with the format mask YYYY"-"MM"-"DD"T"HH24":"MI":"SS. The preview shows 60 rows. Create REST Data Source.

The profile is the same idea as Part 2's: a column per value, with a selector saying where in the reply it comes from. Open Edit Data Profile and make two changes Lesson 4.3 needs:

Try it. On any page, a Classic Report with Location REST Source and REST Source NOAA Kp index lists the week of Kp values, fetched as the page renders.

Lesson 4.2 — The ISS from CelesTrak

Outcome: a data source with a parameter, and the ISS's passes over your home.

The question. CelesTrak serves orbits for thousands of satellites from one URL, chosen by catalog number. How does a data source take an input?

The decision. Parameters. CelesTrak's URL is https://celestrak.org/NORAD/elements/gp.php?CATNR=25544&FORMAT=json: two query-string parameters. The satellite's number varies; the format never does.

1. Create a REST Data Source: Name CelesTrak orbit, URL Endpoint https://celestrak.org/NORAD/elements/gp.php (no ?…). On the remote-server step, set Base URL https://celestrak.org/ and Service URL Path NORAD/elements/gp.php.
2. On Authentication, click Advanced instead of Discover. Add two parameters, both URL Query String:

3. Discover. One row: the ISS (ZARYA), its epoch, mean motion, eccentricity and so on, the six numbers SGP4 needs (SS_ISS, from Before you start). Create REST Data Source.
4. Edit Data Profile, to match SS_SATELLITE_ELEMENTS:

The epoch arrives as text with no time zone (2026-10-24T05:01:11.149248). CelesTrak's epochs are UTC, and the synchronization stores them that way on apex.oracle.com: check in Lesson 4.3 that the table shows +00:00.

From orbit to passes

With the elements in the table, SS_ISS's SGP4 finds every pass over a place in the next three days:

begin
    ss_iss.compute_passes(p_place_id => 3);   -- your home's place id
    commit;
end;

For all nine sample places it takes under a second. visible is Y only when the ISS is sunlit while your sky is dark: the passes worth going outside for.

Lesson 4.3 — Synchronization

Outcome: local copies of all three services that refresh themselves, and a log that says how each run went.

The question. A page could call NOAA every time someone opens it. Why copy the data into a table?

The decision. Synchronize. A local table answers instantly, works when the service doesn't, can be joined to everything else (the aurora chance for your place), and calls the service on its schedule, not once per visitor.

For each data source: open it, Manage Synchronization.

1. Synchronize to Existing Table, Table Name the table below. Save.
2. APEX compares the table with the profile and says the table is different: it has columns the profile doesn't (FETCHED_AT) and the profile has some the table doesn't (A_RUNNING). Synchronization uses the columns both have and ignores the rest. Don't click Alter Table; the table is the one you designed.
3. Synchronization Type Merge, then the schedule with Schedule Builder, Set Execution Interval, Save and Run.

Merge, not Replace. Replace empties the table and reloads it; Merge updates rows that match the primary key and inserts new ones. With Merge, a week of Kp values grows into a history, and an element set that CelesTrak hasn't changed stays as it is.

Check:

select rest_source_name, status, rows_processed, start_timestamp
  from apex_rest_source_sync_log
 where application_id = :APP_ID      -- or your app's number
 order by start_timestamp desc;

The first runs: NOAA 60 rows, CelesTrak 1, NASA 1, all Successful. The CelesTrak row matches what ss_iss.load_elements fetched, value for value, epoch 2026-10-24 05:01:11.149248 +00:00.

fetched_at that tells the truth

Merge updates only the columns in the profile. fetched_at isn't one, so after an update it still holds the time the row was first inserted, and "updated 3 days ago" would be wrong. skysutra_live.sql adds a trigger to each synchronized table:

create or replace trigger ss_space_weather_fetched_trg
    before insert or update on ss_space_weather for each row
begin
    :new.fetched_at := systimestamp;
end;

Lesson 4.4 — Aurora chances and the picture of the day

Outcome: turn raw numbers into answers for one person's sky, and add a third service that needs a key.

Aurora: from Kp to "can I see it here?"

Aurora follow the Earth's magnetic pole, which sits off the geographic one (80.8° N, 72.6° W in 2025). So what matters is your geomagnetic latitude: Wesley Chapel is at 28.2° N but 37.3° geomagnetic.

A common rule of thumb: the aurora's edge sits near geomagnetic latitude 66.5° when Kp is 0 and moves about 2° towards the equator for each step of Kp; from the ground you can see it low on the horizon up to about 10° beyond. ss_live.aurora_outlook turns that into a sentence for a place:

select ss_live.aurora_outlook(3) from dual;
-- No aurora expected here (Kp 2): only the rarest storms reach this far

It's a rule of thumb, and it says so: in the great storm of May 2024, aurora were photographed from Florida.

NASA's picture of the day

Create a third data source, NASA picture of the day, for https://api.nasa.gov/planetary/apod:

1. On Authentication: Authentication Required on, Credentials "Enter new credentials", Authentication Type URL Query String, Name api_key, Value DEMO_KEY. APEX stores this as a web credential (Lesson 4.6) and adds ?api_key=… to every call.
2. Advanced: a URL Query String parameter date, value 2026-10-23, not static; Contains Single Row on. Discover, Create.
3. Edit Data Profile: rename DATE_ to APOD_DATE (primary key) and HDURL to HD_URL; add a Data column COPYRIGHT (selector copyright; not every picture has one, so discovery didn't see it).
4. Edit the date parameter: Default Value type Expression, PL/SQL, to_char(systimestamp at time zone 'America/New_York', 'YYYY-MM-DD'). NASA dates its pictures by US Eastern time, so this asks for today's.
5. Synchronize to SS_APOD, Merge, daily.

Build: the Live card

Run skysutra_live.sql (SQL Scripts), which creates the three triggers and SS_LIVE. Then on page 1, a Classic Report, Live:

with home as (
    select p.*
      from ss_places p
      join ss_users u on u.home_place_id = p.id
     where u.id = ss_auth.current_user_id
)
select 'Space weather' as what,
       ss_live.aurora_outlook(home.id) as now,
       ss_live.freshness((select max(fetched_at) from ss_space_weather)) as freshness
  from home
union all
select 'Next ISS pass',
       nvl((select to_char(starts_at at time zone home.time_zone, 'Dy HH24:MI')
                   || ', up to ' || round(max_altitude_deg) || '°'
                   || case visible when 'Y' then ', visible' else ', in daylight or shadow' end
              from ss_iss_passes
             where place_id = home.id and starts_at > systimestamp
             order by starts_at fetch first 1 row only),
           'None in the next three days'),
       ss_live.freshness((select fetched_at from ss_satellite_elements where norad_id = 25544))
  from home
union all
select 'Picture of the day',
       (select title || case ss_live.apod_repeated(apod_date) when 'Y' then ' (same as yesterday)' end
          from ss_apod order by apod_date desc fetch first 1 row only),
       ss_live.freshness((select max(fetched_at) from ss_apod))
  from dual

Three rows, each with how old it is: "No aurora expected here (Kp 2) · Updated 13 minutes ago", "Sat 10:49, up to 90°, in daylight or shadow · Updated 9 minutes ago", and the picture's title.

Lesson 4.5 — When a service is down

Outcome: an app that keeps working, and says so honestly, when a service stops answering.

The question. SkySutra was built on the US Naval Observatory's API. On October 24, 2026, every call from apex.oracle.com started failing with ORA-29259: end-of-input reached in UTL_HTTP call. What should the app have done?

The decision. Four habits.

Two kinds of "down". A service can fail loudly or quietly:

• Loudly: an error. Calling NASA without a date on the morning of October 24 returned {"code":500,"msg":"Internal Service Error"}. To see what a failed run does, we pointed the date parameter at 1990 (before APOD began) and ran the sync: the log says Failure, ORA-20999: REST Data Source returned an HTTP error: HTTP 400: Bad request, 0 rows processed, and SS_APOD kept its last good row untouched.
• Quietly: a 200 OK with the wrong content. The same week, NASA's API answered every date with the same placeholder: the title "NASA Science", the NASA logo as the picture, and one Rosetta explanation. The sync reported success. That's what ss_live.apod_repeated is for: it notices when today's explanation is word-for-word yesterday's, and the Live card says "(same as yesterday)".

Check a service's content, not just its status code, before you show it as news.

Lesson 4.6 — Web credentials for keys

Outcome: keep every key out of code, exports and screenshots.

The question. NASA's key is just ?api_key=… on the URL. Why not type it into the data source's URL?

The decision. A web credential: the key is stored once, encrypted, in the workspace, and APEX adds it to the request. Nothing that gets exported, committed or shared contains it.

Lesson 4.4 created Credentials for NASA picture of the day (URL Query String, api_key). See it under Workspace Utilities › Web Credentials.

Get your own key. DEMO_KEY is shared by everyone, and NASA limits it to about 30 requests an hour and 50 a day per IP address. On apex.oracle.com, everyone's database calls come from the same few addresses, so the demo key runs out early in the day. Request a free key at api.nasa.gov (it arrives by email), then edit the credential and replace the value. You never change the data source, the sync, or the page.

Checkpoint

SkySutra now keeps itself up to date: the Kp index every three hours, the ISS's orbit every six, NASA's picture every morning. Part 5 turns all of it into the Tonight page.

Test it

1. Three REST Data Sources, each with an active synchronization and a successful first run in apex_rest_source_sync_log.
2. SS_SPACE_WEATHER has about 60 rows; SS_SATELLITE_ELEMENTS has the ISS with an epoch in +00:00; SS_APOD has today's row.
3. ss_iss.compute_passes has filled SS_ISS_PASSES for your home.
4. The home page's Live card shows three rows, each with a freshness label.

Files

Exercises

• Add a sync step to the CelesTrak source for a second satellite: the Hubble Space Telescope is catalog number 20580. Then ss_iss.compute_passes(3, p_norad => 20580).
• NOAA also publishes a 3-day Kp forecast (https://services.swpc.noaa.gov/products/noaa-planetary-k-index-forecast.json). Discover it and show tomorrow night's aurora outlook.
• Make the Live card's freshness label red when a value is more than a day old.
• Stretch: write ss_live.sync_health, which reads apex_rest_source_sync_log and returns the data sources whose last run failed.

Review questions

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