Integrations and AI

Turnout now reaches outside itself three ways: a weather forecast for every upcoming event, an AI that drafts event descriptions, and the phone's camera to scan tickets at the door.

The rule for anything outside the database: pages never wait on the outside world. The weather call happens in a scheduled job and lands in a cache table; the Event page reads the table. If Open-Meteo is slow or down, the page still loads in milliseconds, with yesterday's forecast or none.

Before you start

You need Turnout as it was at the end of Part 10. If you don't have it, import the Start here app, turnout-app-part-10.zip, from the downloads at the end of this page.

Refresh the sample data. The sample events are dated from the day you last ran the seed, and the early ones are only a few days out. In SQL Workshop › SQL Scripts, run turnout_seed.sql again so they're upcoming again. It resets the sample data, including anything you changed while testing.

One new package: in SQL Workshop › SQL Scripts, upload and run to_weather_api.sql. The cache table it writes to, TO_EVENT_WEATHER, has been in your schema since Part 2.

Check: in SQL Commands, run this. It calls Open-Meteo for every event in the next week and prints how many forecasts are cached:

declare
    l_count pls_integer;
begin
    to_weather_api.refresh_upcoming;
    commit;
    select count(*) into l_count from to_event_weather;
    dbms_output.put_line(l_count || ' forecasts cached');
end;

If it prints 0, check that the events have venues with coordinates (the seed's do) and that your workspace can reach the internet; apex.oracle.com can.

Lesson 11.1 Calling a web service from PL/SQL

apex_web_service.make_rest_request sends an HTTP request and returns the body as a CLOB. For a forecast on the day of the event, at the venue:

l_url := 'https://api.open-meteo.com/v1/forecast'
      || '?latitude='   || to_char(l_lat, 'FM990.0000', 'NLS_NUMERIC_CHARACTERS=''.,''')
      || '&longitude='  || to_char(l_lon, 'FM9990.0000', 'NLS_NUMERIC_CHARACTERS=''.,''')
      || '&daily=weather_code,temperature_2m_max,temperature_2m_min,precipitation_probability_max'
      || '&timezone='   || apex_util.url_encode(l_tz)
      || '&start_date=' || l_day
      || '&end_date='   || l_day;

l_response := apex_web_service.make_rest_request(p_url => l_url, p_http_method => 'GET');

Three details that bite people:

• Decimal points. to_char(27.95) prints 27,95 in a database set up for Spanish or German. NLS_NUMERIC_CHARACTERS forces a point, which every web service expects.
• Encode what you put in a URL. The time zone America/New_York contains a slash; apex_util.url_encode makes it safe.
• Check the status. apex_web_service.g_status_code holds the HTTP status. Anything but 200 keeps the last good forecast.

Lesson 11.2 Reading JSON, and caching it

Open-Meteo answers with arrays, one value per day. json_value reads the first:

select json_value(l_response, '$.daily.temperature_2m_max[0]' returning number), ...

The result goes into TO_EVENT_WEATHER with a MERGE: update the row if the event has one, insert if not. A WMO weather code becomes words with a case: 0 is Clear, 61 to 67 are Rain, 95 to 99 are Thunderstorms.

The Refresh weather automation calls to_weather_api.refresh_upcoming every 3 hours for published events in the next 7 days whose venue has coordinates. Each event's call sits in its own begin ... exception block, so one failed call doesn't stop the rest.

Build it. Shared Components › Automations › Create: Name Refresh weather, Scheduled, Actions initiated on Always, Execution Schedule Custom with Frequency Hourly and Interval 3. Create, set Schedule Status Active, and add one action, Execute Code, PL/SQL:

to_weather_api.refresh_upcoming;

Lesson 11.3 Showing it

The Event page's load process adds one line, :P20_WEATHER := to_weather_api.describe(:P20_EVENT_ID);, which reads the cache:

Partly cloudy · 64 to 75°F (18 to 24°C) · 20% chance of rain

The item has the condition Item is not null, so events without a forecast simply don't show the line.

Build it.

1. Open page 20. Right-click the Event region, Create Page Item: Name P20_WEATHER, Type Display Only, Label Forecast, Sequence 95, Template Optional - Above, Server-side Condition Item is NOT NULL, item P20_WEATHER.
2. Processing tab, process Load event. Find the select ... into :P20_TITLE, :P20_CLUB, ... statement. Right after its closing semicolon, before l_left := to_rsvp_api.spots_left(...), add:

:P20_WEATHER := to_weather_api.describe(:P20_EVENT_ID);

Check: open Saturday Long Run. Under the details there's a line like "Partly cloudy · 64 to 75°F (18 to 24°C) · 20% chance of rain". An event more than a week away has none.

Lesson 11.4 Draft with AI

Organizers hate writing descriptions. On the event editor, Draft with AI turns a title, a time and a few notes into two or three friendly sentences, which the organizer then edits. Nothing is saved until they press Save.

Bring your own service

AI calls cost money, so each workspace brings its own key:

1. Get an API key from a provider APEX supports: OpenAI, OCI Generative AI, or Cohere.
2. Shared Components › AI Services › Create (the Generative AI Services page). Pick the Provider, paste the key, choose a Model, and give it the Static ID TURNOUT_AI.
3. Shared Components › Application Settings › Create: Name AI_SERVICE, Value TURNOUT_AI, Required off, On Upgrade Keep Value on. No key? Create the setting anyway, with the value empty.

The button stays hidden while AI_SERVICE is empty, so a copy of Turnout without a key never shows a button that fails.

The call

A dynamic action runs server-side code, the pattern from Lesson 8.3:

:P120_DESCRIPTION := apex_ai.generate(
    p_system_prompt     => 'You write short, friendly event descriptions for a '
                        || 'community club app. Two or three sentences, plain words, '
                        || 'no emoji, no exclamation marks. Say who it suits and what '
                        || 'to bring. Never invent prices, distances or names.',
    p_prompt            => 'Event: ' || :P120_TITLE
                        || chr(10) || 'Starts: ' || :P120_STARTS
                        || chr(10) || 'Ends: '   || :P120_ENDS
                        || chr(10) || 'Notes from the organizer: ' || :P120_DESCRIPTION,
    p_service_static_id => apex_app_setting.get_value('AI_SERVICE'));

The system prompt is the product. It sets the voice (plain, no exclamation marks: the Turnout voice from Part 9) and, just as important, what the model must not do. "Never invent prices, distances or names" stops a description promising a free breakfast nobody planned.

People stay in charge. The draft lands in the field, editable. The organizer reads it, fixes it and saves. An AI that writes straight to the database would publish its mistakes.

Build it.

1. Open page 120. Right-click the Buttons region, Create Button: Button Name DRAFT_WITH_AI, Label Draft with AI, Slot Delete, Sequence 15, Template Options › Type Simple, Action Defined by Dynamic Action, Server-side Condition Expression, apex_app_setting.get_value('AI_SERVICE') is not null.
2. Dynamic Actions tab: right-click Click, Create Dynamic Action, Name Draft the description with AI, Button DRAFT_WITH_AI.
3. True action Execute Server-side Code, PL/SQL, the code above. Items to Submit P120_TITLE,P120_STARTS,P120_ENDS,P120_DESCRIPTION, Items to Return P120_DESCRIPTION, Show Processing on, Fire on Initialization off.

Check: with AI_SERVICE empty, the button isn't there. With it set, open an event, type bring water in About this event, and press Draft with AI: after a moment the field holds a short description. Close without saving and nothing changed.

Lesson 11.5 Scanning tickets with the camera

Modern browsers can read QR codes themselves, through the BarcodeDetector API: no library to download. The check-in page's Scan with camera button runs JavaScript that:

1. Checks the browser has BarcodeDetector; if not, says to type the code or use a scanner app.
2. Opens the back camera with navigator.mediaDevices.getUserMedia({ video: { facingMode: 'environment' } }) and shows it in a <video> element.
3. Every quarter second, asks detector.detect(video) for QR codes.
4. On the first one, stops the camera, puts the code in P130_TICKET, and submits the page as if the volunteer had typed it.

From there it's the same check-in as Lesson 7.12: the event check, then to_rsvp_api.check_in.

Where it works. Chrome and Edge on Android support BarcodeDetector today; Safari doesn't yet. The fallback, typing or a scanner app, always works, so no one is stuck at the door. Browsers also only allow the camera on HTTPS pages, which apex.oracle.com and any production APEX are.

Build it.

1. Open page 130. Right-click Body, Create Region: Name Camera, Type Static Content, Sequence 25 (between Ticket and Here so far), Template Blank with Attributes, and this Source › HTML Code:

<video id="tn-scan" playsinline muted style="display:none;width:100%;max-width:420px;border-radius:12px"></video>

2. Right-click the Ticket region, Create Button: Button Name SCAN_CAMERA, Label Scan with camera, Slot Edit, Action Defined by Dynamic Action.
3. Dynamic Actions tab: right-click Click, Create Dynamic Action, Name Scan with camera, Button SCAN_CAMERA. True action Execute JavaScript Code, Fire on Initialization off, with:

if (!('BarcodeDetector' in window)) {
    apex.message.alert("This browser can't scan with the camera. Use a scanner app, or type the code.");
    return;
}
const video    = document.getElementById('tn-scan'),
      detector = new BarcodeDetector({ formats: ['qr_code'] });
navigator.mediaDevices.getUserMedia({ video: { facingMode: 'environment' } })
    .then(stream => {
        video.srcObject = stream;
        video.style.display = 'block';
        video.play();
        const look = () => detector.detect(video).then(codes => {
            if (codes.length) {
                stream.getTracks().forEach(t => t.stop());
                video.style.display = 'none';
                $s('P130_TICKET', codes[0].rawValue);
                apex.page.submit('CHECK_IN');
            } else {
                setTimeout(look, 250);
            }
        });
        look();
    })
    .catch(() => apex.message.alert('The camera is blocked. Allow it in your browser settings, or type the code.'));

Check: in Safari or Firefox the button says the browser can't scan. In Chrome on Android it asks for the camera, then checks in the first ticket it sees.

Checkpoint

Try it

1. If you skipped the check in Before you start, run it now.
2. Open an event in the next week: there's a Forecast line.
3. If you have an AI key, set up TURNOUT_AI and AI_SERVICE, open an event in the editor, type "bring water" in the description, and press Draft with AI.
4. As Priya, create and publish a Tampa Runners event starting in 30 minutes. On a second phone, sign in as Hannah, RSVP to it and open My ticket. On an Android phone signed in as Priya, open Check-in for that event and scan Hannah's ticket.

Files

Exercises

• Show the forecast on the cards on My events, with a weather icon from Font APEX.
• Warn organizers on the dashboard when an outdoor event's chance of rain is over 60%.
• Use apex_ai.generate to suggest three event titles from a club's past events.
• Stretch: add the APEX 26.2 AI Assistant to page 100 so organizers can ask "who hasn't come in a month?"