The observing log

So far SkySutra has only told people about the sky. In Part 8 they write back. You build an observing log where people record what they saw, with photos. A moderator looks at anything meant for other people before it's published, and others can comment on it. On the way you meet four APEX features that work together: the Image Upload item, Task Definitions, Workflows and the Unified Task List.

How Part 8 works

Before you start

You need Part 7 done. Download skysutra_observations.sql and run it in SQL Scripts. It creates:

• the table SS_OBSERVATION_COMMENTS
• the package SS_OBS_API, which makes every change to an observation
• the view SS_OBSERVATIONS_V, which the pages read

Then run the new skysutra_places.sql again. Only its last view changed: the map now shows shared observations as well as public ones, and only once they're published.

The rule for this part

SS_OBSERVATIONS.STATUS is never set by a page. A page asks SS_OBS_API to save or submit an observation, and the workflow asks it to publish or send one back. Each procedure checks who's asking and what state the observation is in:

A private observation is for your eyes only, so it needs no moderator: Submit publishes it at once and the log calls it "Saved". Only something other people will see waits for review.

Who can see what is decided in one place, the view:

 where o.user_id = ss_auth.current_user_id                                         -- yours, any status
    or (o.status = 'PUBLISHED' and o.visibility = 'PUBLIC')                         -- everyone's
    or (o.status = 'PUBLISHED' and o.visibility = 'SHARED'
        and ss_auth.current_user_id is not null)                                     -- for signed-in people

Every page reads SS_OBSERVATIONS_V, so no page can forget the rule. ss_obs_api.can_see applies the same test to one observation, for the photo and comment queries.

Lesson 8.1 — Logging an observation

Outcome: a dialog to log an observation with a photo, and a log that shows them as cards.

The question. What did you see, when, from where, and how good was the sky? An observation's target can be one of four kinds: a star, a planet or the Moon, a constellation, or something else (a meteor, a satellite flare, a comet). The table keeps them in four columns, with a check constraint that exactly one is filled in.

Build: the Observation dialog

1. Create Page › Blank Page: 51, Observation, Page Mode Modal Dialog, no breadcrumb or navigation.

2. Add a region Observation (Static Content, template Blank with Attributes) and a region Buttons (template Buttons Container, slot Dialog Footer).

3. Add these items to Observation. Put pairs side by side by turning Start New Row off on the second item.

   Item	Type	Notes
   P51_ID, P51_STATUS_CODE	Hidden
   P51_STATUS	Display Only	"New", "Draft", "Sent back"…
   P51_STAR_ID	Popup LOV	Named stars and anything brighter than magnitude 4
   P51_BODY_ID	Select List	select name d, id r from ss_bodies order by id
   P51_CONSTELLATION_ID	Select List
   P51_OTHER_TARGET	Text Field	"Something else"
   P51_PLACE_ID	Select List, required	Your places, then the observing sites, from SS_MAP_PLACES_V
   P51_OBSERVED_AT	Date Picker, Show Time on, format YYYY-MM-DD HH24:MI, required
   P51_SEEING, P51_TRANSPARENCY	Select List, static 1–5	Seeing: Boiling…Steady. Transparency: Hazy…Crystal clear
   P51_INSTRUMENT	Text Field
   P51_NOTES	Textarea
   P51_VISIBILITY	Radio Group, required, 3 columns	STATIC:Only me;PRIVATE,Signed-in stargazers;SHARED,Everyone;PUBLIC. Turn Display Null Value off, or the group gets a fourth, empty choice
   P51_PHOTO	Image Upload	Storage: Table APEX_APPLICATION_TEMP_FILES; Max Width and Height 2000
   P51_CAPTION	Text Field

   The star list:

   select coalesce(s.proper_name, s.bayer) || ' (' || c.name || ', mag '
          || to_char(s.magnitude, 'fm90.0') || ')' as d,
          s.id as r
     from ss_stars s
     join ss_constellations c on c.id = s.constellation_id
    where s.proper_name is not null or s.magnitude < 4
    order by s.magnitude
   

   It's a Popup LOV rather than a Select List because it has over 400 rows: people type "veg" and pick Vega.

4. The Image Upload item. It's the picture-friendly cousin of File Browse. People can drop, pick or (on a phone) take a photo, and the item shows a preview. With Maximum Width and Height set, the browser shrinks a 12-megapixel phone photo to 2000 pixels before uploading it, so a 5 MB file arrives as a few hundred kB. On submit, the file lands in APEX_APPLICATION_TEMP_FILES, and the item's value is the file's NAME there. It stays for the session; ss_obs_api.add_photo copies it into SS_OBSERVATION_PHOTOS:

   insert into ss_observation_photos (observation_id, image, mime_type, file_name, caption)
   select p_observation_id, f.blob_content, f.mime_type, f.filename, p_caption
     from apex_application_temp_files f
    where f.name = p_temp_file;
   

5. Buttons in Buttons:

   • Cancel (slot Close): action Redirect to URL javascript:apex.navigation.dialog.cancel(true);
   • Save Draft (slot Create): Submit Page
   • Submit (slot Next, Hot): Submit Page

   Give Save Draft and Submit a server-side condition, the PL/SQL expression nvl(:P51_STATUS_CODE, 'DRAFT') in ('DRAFT', 'RETURNED'). An observation waiting for a moderator, or already published, opens read-only in effect: there's nothing to press.

6. Pre-Rendering › Before Header, process Load the observation:

   if :P51_ID is not null then
       -- Only your own observations open here; anyone else's is a no_data_found
       select o.star_id, o.body_id, ..., o.status, v.status_label
         into :P51_STAR_ID, :P51_BODY_ID, ..., :P51_STATUS_CODE, :P51_STATUS
         from ss_observations o
         join ss_places p on p.id = o.place_id
         join ss_observations_v v on v.id = o.id
        where o.id = :P51_ID
          and o.user_id = ss_auth.current_user_id;
   else
       -- A new observation: from home, now, only for you
       select max(u.home_place_id),
              max(to_char(systimestamp at time zone p.time_zone, 'YYYY-MM-DD HH24:MI'))
         into :P51_PLACE_ID, :P51_OBSERVED_AT
         from ss_users u
         left join ss_places p on p.id = u.home_place_id
        where u.id = ss_auth.current_user_id;
       :P51_VISIBILITY := 'PRIVATE';
       :P51_STATUS     := 'New';
   end if;
   

   The time is shown in the place's time zone, with at time zone p.time_zone, not the server's (UTC) or the browser's. Someone in Tampa logging a night in Pune means 21:00 in Pune.

7. Processing, process Save the observation, server-side condition Request is contained in Value SAVE,SUBMIT:

   declare
       l_tz ss_places.time_zone%type;
   begin
       -- "When" is typed in the place's own time zone
       select time_zone into l_tz from ss_places where id = :P51_PLACE_ID;
   
       :P51_ID := ss_obs_api.save(
                      p_id          => :P51_ID,
                      p_star_id     => :P51_STAR_ID,
                      ...
                      p_observed_at => from_tz(to_timestamp(:P51_OBSERVED_AT, 'YYYY-MM-DD HH24:MI'), l_tz),
                      ...
                      p_visibility  => :P51_VISIBILITY);
   
       -- The Image Upload item holds the name of a row in APEX_APPLICATION_TEMP_FILES
       ss_obs_api.add_photo(:P51_ID, :P51_PHOTO, :P51_CAPTION);
   
       if :REQUEST = 'SUBMIT' then
           ss_obs_api.submit(:P51_ID);
           apex_application.g_print_success_message :=
               case :P51_VISIBILITY
                   when 'PRIVATE' then 'Saved to your log.'
                   else 'Sent to a moderator. You''ll see it published once it''s approved.'
               end;
       else
           apex_application.g_print_success_message := 'Draft saved.';
       end if;
   end;
   

   Then a Close Dialog process with the same condition. The success message set in code travels with the closing dialog to the page underneath.

   If someone fills in no target, or two, save raises ORA-20301 "Choose one thing you observed…", and APEX shows the message on the dialog. The check lives in the package (and in the table's constraint), not in a page validation, so it holds for every way in.

Build: the Observing Log

1. Create Page › Blank Page: 50, Observing Log, with a navigation entry (icon fa-book).

2. Region My observing log, type Cards, template Standard, source SQL Query:

   select v.id, v.target, v.target_kind, v.observed_at, v.observed_local,
          v.status, v.status_label,
          v.place || ' · ' || v.visibility_label
            || case when v.photos = 1 then ' · 1 photo' when v.photos > 1 then ' · ' || v.photos || ' photos' end
            || case when v.comments = 1 then ' · 1 comment' when v.comments > 1 then ' · ' || v.comments || ' comments' end
                                                                       as details,
          ph.image                                                     as photo,
          ph.mime_type                                                 as photo_mime,
          case v.status when 'PUBLISHED' then 'u-success'
                        when 'IN_REVIEW' then 'u-warning'
                        when 'RETURNED'  then 'u-danger'
                        else 'u-color-16' end                          as status_css
     from ss_observations_v v
     left join ss_observation_photos ph on ph.id = v.first_photo_id
    where v.is_mine = 'Y'
   

   Order By Static Value observed_at desc.

3. Region Attributes:

   • Grid, 3 columns.
   • Title TARGET, Subtitle OBSERVED_LOCAL, Secondary Body DETAILS.
   • Badge STATUS_LABEL with CSS classes &STATUS_CSS..
   • Media Source BLOB Column: PHOTO, Mime Type Column PHOTO_MIME, position First, Cover. The primary key column ID tells APEX which row's image to fetch.
   • Lazy Loading on.

   A Cards region serves BLOB images itself, so there's no download procedure to write.

4. Actions › Full Card, redirect to page 52, setting P52_ID to &ID., clearing page 52's cache.

5. A button Log an Observation (slot Create, Hot, icon fa-plus), redirecting to page 51 with page 51's cache cleared.

6. A second Cards region, From other stargazers, with the same attributes and where v.is_mine = 'N'. Its details show the observer instead of the visibility. The view already hides anything unpublished.

7. A dynamic action on the My observing log region: event Dialog Closed, action Submit Page. The cards refresh and the success message shows.

Lesson 8.2 — A human task

Outcome: a task a moderator can approve or reject, with the observation's details in front of them.

The question. Who decides that a shared observation is fit to publish, and how do they know there's something waiting? A Task Definition is APEX's answer. It's a template for a piece of work a person must do: its subject, who may do it, what they see, and the outcomes. Each time one is needed, APEX creates a task from it, and it shows up in the right people's inbox.

Build: Review an observation

Shared Components › Task Definitions › Create:

The actions query gives the task its columns. :APEX$TASK_PK is the observation's ID, passed in when the task is created:

select o.id,
       coalesce(s.proper_name, s.bayer, b.name, c.name, o.other_target) as target,
       u.display_name as observer,
       p.name         as place,
       o.visibility,
       o.notes
  from ss_observations o
  join ss_users  u on u.id = o.user_id
  join ss_places p on p.id = o.place_id
  left join ss_stars s          on s.id = o.star_id
  left join ss_bodies b         on b.id = o.body_id
  left join ss_constellations c on c.id = o.constellation_id
 where o.id = :APEX$TASK_PK

Read the query's own tables, not SS_OBSERVATIONS_V. The view only shows an observation in review to its owner, and the moderator isn't the owner.

Potential owners from an authorization scheme means nobody has to keep a list. Anyone with the Moderator role (Maya, from Lesson 0.6) sees the task; whoever claims it first owns it. The business administrator can reassign or cancel a task that's stuck.

Initiator Can Complete: No. The person who submitted the observation mustn't approve their own. It matters here because Neelesh is both an administrator and a stargazer.

On the definition's page, click Create Task Details Page. APEX generates page 54, the page a moderator opens from the inbox: the subject, the actions query's columns, Approve and Reject buttons, and a comment box. Nothing on it needs changing.

Lesson 8.3 — A workflow

Outcome: a workflow that starts on Submit, waits for the moderator, and publishes or sends back.

The question. A task is one person's decision. What happens before and after it? A Workflow strings steps together: the task, a decision on its outcome, and what to do on each path. It runs in the database, waits as long as it has to (days, if the moderator's away), and keeps a history you can inspect.

Why not just call the task from Submit? You could: apex_approval.create_task and an action on the task for "on approve". The workflow puts the whole path in one diagram, and Part 10 adds steps to it (a push notification to the owner) without touching any page.

Build: Observation review

Shared Components › Workflows › Create. Name Observation review, static ID OBSERVATION_REVIEW. It creates version 1, in development.

1. Version › Additional Data, SQL Query. Like the task's actions query, it reads the observation by :APEX$WORKFLOW_DETAIL_PK, which is what start_workflow passed as p_detail_pk. Its columns (TARGET, OBSERVER, …) can be used in the workflow's title: Review of &TARGET..

2. Variables: APPROVER (Varchar2), TASK_ID (Number), TASK_OUTCOME (Varchar2).

3. Activities, from the palette onto the diagram:

   Activity	Type	Settings
   Start	Start
   A moderator reviews it	Human Task - Create	Definition: Review an observation. Task ID Item: TASK_ID. Details Primary Key Item: APEX$WORKFLOW_DETAIL_PK. Outcome: TASK_OUTCOME. Owner: APPROVER
   Approved?	Switch, True/False Check	Workflow variable TASK_OUTCOME equals APPROVED
   Publish it	Execute Code	ss_obs_api.publish(:APEX$WORKFLOW_DETAIL_PK);
   Send it back	Execute Code	ss_obs_api.send_back_after_review(:APEX$WORKFLOW_DETAIL_PK, :TASK_ID);
   End	End

4. Connect them: Start → review → Approved?; from the switch, the True branch (name it Approved) → Publish it and the False branch (Rejected) → Send it back; both → End.

The Human Task activity waits. The workflow pauses there until the task is completed, then sets TASK_OUTCOME to APPROVED or REJECTED and moves on.

send_back_after_review takes the moderator's latest comment on the task (from APEX_TASK_COMMENTS) and adds it to the observation's comments as "Sent back by a moderator: …". The owner sees the reason where they'll look, on the observation itself.

5. Participants: a Workflow Owner (your own user name). APEX won't activate a version without one. The owner can see, suspend and retry the workflow's instances in the Workflow Console.
6. Right-click version 1 [Dev] › Activate. A version in development only runs in a builder session; an active one runs for everyone. To change an active workflow later, you create version 2 next to it. Instances already running finish on the version they started on.

Starting it

ss_obs_api.submit starts it, then marks the observation as in review:

-- Start the workflow first, in its own statement
l_wf_id := apex_workflow.start_workflow(
               p_static_id  => 'OBSERVATION_REVIEW',
               p_detail_pk  => p_observation_id,
               p_initiator  => sys_context('APEX$SESSION', 'APP_USER'));
update ss_observations
   set status = 'IN_REVIEW', workflow_id = l_wf_id
 where id = p_observation_id;

It's tempting to write set workflow_id = apex_workflow.start_workflow(...) in one UPDATE. Don't. start_workflow runs the workflow's Additional Data query straight away, and that query reads SS_OBSERVATIONS, the table the UPDATE is changing. Oracle refuses: "ORA-04091: table SS_OBSERVATIONS is mutating", wrapped in APEX's "Additional data row associated with this workflow instance is not found". It's the same trap as Lesson 5.1: a function called from a statement mustn't read the table that statement is changing. Two statements fix it.

They're still one transaction (the page process commits at the end). If start_workflow fails, nothing is saved, and the observation is never stuck "in review" with nobody reviewing. (On apex.oracle.com you can't try this from SQL Commands: apex_session.create_session is blocked there, so start workflows from the app.)

Lesson 8.4 — The moderator's inbox

Outcome: a page where a moderator sees and works on the tasks waiting for them.

Build: Review Queue

1. Create Page › Unified Task List: 53, Review queue, Report Context My Tasks.
2. The page's Authorization Scheme: Moderator. In Shared Components › Navigation Menu, rename its entry Review Queue and give it the same authorization scheme, so only moderators see it in the menu.

The wizard builds a Content Row report on APEX_TASKS with smart filters, plus the buttons to claim, approve and reject from the list. A task's subject links to its details page (54).

Report Context decides which tasks are listed:

Lesson 8.5 — Comments

Outcome: an observation's own page, with its photos and a conversation under it.

Build: Observation Details

1. Create Page › Blank Page: 52, Observation Details, breadcrumb on, no navigation entry. Page title &P52_TARGET..

2. Region Details (Static Content) with hidden P52_ID, P52_IS_MINE, P52_STATUS_CODE, P52_TARGET, and Display Only items What, When, Where, Observer, Conditions, Instrument, Status, Notes.

3. Before Header, Load the observation: one select * into l_obs from ss_observations_v where id = :P52_ID, then the items. When the view doesn't return it (someone else's draft, or a private one), no_data_found becomes "That observation isn't there, or it isn't shared with you." Nobody can read an observation by guessing P52_ID.

4. Buttons on Details:

   • Observing Log (back to page 50).
   • Edit (Hot): to page 51 with P51_ID = &P52_ID., shown when :P52_IS_MINE = 'Y' and :P52_STATUS_CODE in ('DRAFT', 'RETURNED').

   Add a Dialog Closed › Submit Page dynamic action on the region, as on page 50.

5. Region Photos, Cards on ss_observation_photos where observation_id = :P52_ID and ss_obs_api.can_see(:P52_ID) = 'Y'. Title CAPTION; media BLOB column IMAGE, Fit; two columns.

6. Region Comments, type Comments (a template component in the Universal Theme):

   select c.id,
          u.display_name                           as user_name,
          apex_string.get_initials(u.display_name) as initials,
          c.body                                   as comment_text,
          c.created_at
     from ss_observation_comments c
     join ss_users u on u.id = c.user_id
    where c.observation_id = :P52_ID
      and ss_obs_api.can_see(:P52_ID) = 'Y'
   

   Its attributes map columns to the parts of a comment: Comment Text COMMENT_TEXT, User Name USER_NAME, Date CREATED_AT (give the column the format mask SINCE, for "2 hours ago"), avatar type Initials from INITIALS. Order by created_at.

7. Region Add a comment (Blank with Attributes, shown when the user is not the public user): a Textarea P52_COMMENT and a button Add Comment. Process Add the comment, when Add Comment is pressed:

   ss_obs_api.add_comment(:P52_ID, :P52_COMMENT);
   :P52_COMMENT := null;
   

   add_comment checks can_see too. A page item can be changed in the browser, so the database checks again.

The moderator's "sent back" reason arrives here as the first comment, and the owner can reply under it before submitting again.

Lesson 8.6 — How-to: photos in Object Storage (optional)

SkySutra keeps photos as BLOBs (MODEL decision 3). It's the simplest thing that works: one backup, one security rule, and the Cards region serves them for free. On apex.oracle.com it's also the only option, since you can't create cloud storage there. With thousands of people logging photos, though, the database fills up with pictures. That's what Object Storage is for.

This how-to needs your own Oracle Cloud account (the Free Tier is enough) and an Autonomous Database or another APEX instance you control. We haven't run it on the course workspace.

1. A bucket. In the Oracle Cloud console, Storage › Buckets › Create Bucket: skysutra-photos, Standard tier, private. Note your namespace and region (for example us-ashburn-1).

2. An API key for a user (or better, a dedicated one) with a policy allowing manage objects in that bucket's compartment. Profile › API keys › Add API key gives you the private key, fingerprint, tenancy OCID and user OCID.

3. A web credential in APEX, Workspace Utilities › Web Credentials: type Oracle Cloud Infrastructure (OCI), with the values from step 2. Give it the static ID OCI_PHOTOS. The key lives in the credential, never in code.

4. Upload in add_photo, instead of the insert:

   l_url := 'https://objectstorage.' || c_region || '.oraclecloud.com/n/' || c_namespace
            || '/b/skysutra-photos/o/' || apex_util.url_encode(l_object_name);
   apex_web_service.g_request_headers(1).name  := 'Content-Type';
   apex_web_service.g_request_headers(1).value := l_mime_type;
   l_response := apex_web_service.make_rest_request(
                     p_url                  => l_url,
                     p_http_method          => 'PUT',
                     p_body_blob            => l_blob,
                     p_credential_static_id => 'OCI_PHOTOS');
   if apex_web_service.g_status_code <> 200 then
       raise_application_error(-20320, 'The photo couldn''t be stored (' || apex_web_service.g_status_code || ').');
   end if;
   

   Store the object name (obs-<id>-<photo id>.jpg) in the photos table instead of the image.

5. Showing it. The bucket is private, so a page can't link to it directly. Either:

   • create a pre-authenticated request per photo (read-only, expiring), store its URL, and use Media Source URL Column in the cards; or
   • write a small procedure that fetches the object with the same credential and streams it with wpg_docload, and point the cards at it. That's slower, but nothing is ever public.

The trade-off: Object Storage is cheap and scales, but security now lives in two places (the database and the bucket's links), and backups, deletes and the "who can see this" rule have to cover both. For a course app, the BLOB is the right call.

Checkpoint

People can now keep an observing log with photos. Moderators approve what's shared, and anyone signed in can comment.

Test it

You need two browsers (or a private window): one signed in as yourself, one as Maya.

1. Observing Log › Log an Observation: pick a star, leave Only me, add a photo, Submit. "Saved to your log." The card shows the photo and a green Saved badge.
2. Log another, this time for Signed-in stargazers, and Submit. "Sent to a moderator." Its badge says Waiting for a moderator, and there's no Edit on its page.
3. As Maya: Review Queue lists "Review: … by …". Open it, add a comment ("Could you add the time you saw it?"), Reject.
4. As yourself: refresh the log. Sent back, and on its page the first comment reads "Sent back by a moderator: Could you add the time you saw it?". Edit, change the notes, Submit again.
5. As Maya: Approve. As yourself: Published. Sign in as Ben: he sees it under From other stargazers and can comment; he can't open your private observation by changing P52_ID in the URL.
6. Shared Components › Workflows › Observation review, or the Workflow Console: each submission is one instance, with its activities and timings.

Files

Exercises

• Let people delete a draft (and its photos) from the Observation dialog, with a confirmation.
• Show a photo's caption on the card when it has one, and let the owner delete a photo on page 52 (ss_obs_api.delete_photo is already there).
• Add a third outcome for the moderator, "Needs a better photo", as a second Switch branch with its own comment.
• Give the Review Queue a badge in the navigation menu with the number of tasks waiting (a list entry badge from apex_tasks).
• Stretch: a deadline. If nobody reviews an observation in three days, the task expires; add a path in the workflow that publishes it anyway, or tells an administrator.

Review questions

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