Enhancing User Experience in Oracle APEX – Implementing Video Watch Tracking with Resume & Dynamic Video Selection Using SharePoint URLs

 

🚀 Introduction

In modern enterprise applications such as Learning Management Systems (LMS), onboarding portals, and compliance training platforms, tracking how much of a video a user has watched is extremely important.

Instead of simply marking content as viewed, organizations often need to:

• Monitor viewing progress

• Ensure full completion

• Resume playback from last watched position

• Prevent users from skipping content

To address this requirement, I developed a Video Tracking Mechanism in Oracle APEX integrated with SharePoint-hosted videos that captures real-time watch progress and stores it in the database.

This solution enhances user experience while enabling accurate engagement tracking.

🎯 Objective

The goal of this implementation was to:

✔️ Track video watch percentage
✔️ Display real-time progress
✔️ Resume playback automatically
✔️ Mark videos as fully watched
✔️ Maintain user-wise tracking
✔️ Stream videos directly from SharePoint
✔️ Support multiple videos dynamically

🧩 Solution Overview

This solution combines:

• HTML5 Video Player

• JavaScript Event Tracking

• Oracle APEX AJAX Processes

• Database-based progress storage

• LOV-based dynamic video selection

• SharePoint-hosted video URLs

This ensures a seamless interaction between the user interface and backend tracking logic.

🧱 Step-by-Step Approach

Step 1:🗄️ Database Structure

To store video viewing progress for each user, the following table was created:

CREATE TABLE VIDEO_PROGRESSS_T (
    USERNAME        VARCHAR2(100)   NOT NULL,
    VIDEO_NAME      VARCHAR2(255)   NOT NULL,
    LAST_POSITION   NUMBER,
    FULLY_WATCHED   VARCHAR2(1) DEFAULT 'N',
    UPDATED_ON      DATE,
    
    CONSTRAINT VIDEO_PROGRESSS_PK
    PRIMARY KEY (USERNAME, VIDEO_NAME)
);

This table stores:

• Last watched timestamp

• Completion status

• Video-wise user tracking

Step 2:🎥 Dynamic Video Selection using LOV

To make the solution scalable, a List of Values (LOV) was implemented so users can choose and play different videos dynamically.

Instead of hardcoding a single video, users can select from multiple videos such as:

• Birds

• Deer

• Monkeys

• Natural Driving

• Linking Bridge

• Truck

• Aeroplan

This allows the system to support multi-video learning environments.

Step 3:📂 LOV Configuration

The LOV was created using SQL:

SELECT 'Birds', 'Birds.mp4' FROM dual
UNION ALL
SELECT 'Deer', 'Deer.mp4' FROM dual
UNION ALL
SELECT 'Monkeys', 'Monkeys.mp4' FROM dual
UNION ALL
SELECT 'Tiger', 'Tiger.mp4' FROM dual
UNION ALL
SELECT 'Sea Fishes', 'Sea_Fishes.mp4' FROM dual
UNION ALL
SELECT 'Natural Driving', 'Driving_in_Nature.mp4' FROM dual
UNION ALL
SELECT 'Linking Bridge', 'Link_Bridge.mp4' FROM dual
UNION ALL
SELECT 'Truck', 'Truck.mp4' FROM dual
UNION ALL
SELECT 'Landing Plane', 'Aeroplane.mp4' FROM dual;

Here:

• Display Value → Video Name

• Return Value → MP4 File

Videos were uploaded as Static Application Files.

Step 4: 🎬 UI Video Player

A custom HTML region was used:

<div id="progressBarContainer"
     style="width: 100%; background: #ddd; height: 20px; border-radius: 10px;
            overflow: hidden; margin-top: 5px; margin-bottom: 60px;">  <!-- ⬅ increased -->
  <div id="progressBarFill"
       style="width: 0%; height: 100%; background: linear-gradient(90deg, #4CAF50, #2E7D32);
              transition: width 0.4s ease;"></div>
</div>

<div id="videoContainer" style="display:none;">
  <video id="myVideo" width="1450" height="360" controls>
    <source id="videoSource" src="" type="video/mp4">
    Your browser does not support the video tag.
  </video>
  <p>Watched: <span id="progressPercent">0%</span></p>
</div>

Step 5:📊 Real-Time Progress Tracking

JavaScript was used to monitor video playback:

Code:

var video = document.getElementById("myVideo");

video.addEventListener("timeupdate", function () {
    var percentage = (video.currentTime / video.duration) * 100;
    percentage = Math.round(percentage);

    document.getElementById("progressPercent").innerText = percentage + "%";
    $s("P4_VIDEO_PERCENT", percentage);
});

video.addEventListener("ended", function () {
    $s("P4_VIDEO_PERCENT", 100);
});

This enables live tracking of:

• Watch percentage

• Playback progress

Step 6:🔄 Resume Playback

An AJAX process retrieves the last watched position:

👉 GET_VIDEO_POSITION

This ensures that when users revisit the video, playback resumes from where they left off.

Code:

DECLARE

    v_username   VARCHAR2(100) := NVL(:APP_USER, 'UNKNOWN');

    v_video_name VARCHAR2(255) := APEX_APPLICATION.G_X01;

    v_position   NUMBER := 0;

BEGIN

    BEGIN

        SELECT last_position

          INTO v_position

          FROM VIDEO_PROGRESSS_T

         WHERE username = v_username

           AND video_name = v_video_name;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            v_position := 0;

    END;

    -- Return JSON with last position

    APEX_JSON.OPEN_OBJECT;

    APEX_JSON.WRITE('last_position', v_position);

    APEX_JSON.CLOSE_OBJECT;

EXCEPTION

    WHEN OTHERS THEN

        APEX_JSON.OPEN_OBJECT;

        APEX_JSON.WRITE('last_position', 0);

        APEX_JSON.WRITE('error', SQLERRM);

        APEX_JSON.CLOSE_OBJECT;

END;

Step 7: 💾 Save Viewing Progress

Another AJAX process:

👉 SAVE_VIDEO_POSITION

Updates:

• Last watched time

• Completion status

in the database.

Code:

DECLARE

  v_username   VARCHAR2(100) := NVL(:APP_USER, 'UNKNOWN');

  v_video_name VARCHAR2(255) := APEX_APPLICATION.G_X01;

  v_position   NUMBER := NVL(TO_NUMBER(APEX_APPLICATION.G_X02), 0);

  v_fully      VARCHAR2(1)   := NVL(APEX_APPLICATION.G_X03, 'N');

BEGIN

  MERGE INTO VIDEO_PROGRESSS_T t

  USING (

    SELECT v_username AS username,

           v_video_name AS video_name,

           v_position AS last_position,

           v_fully AS fully_watched

    FROM dual

  ) src

  ON (t.username = src.username AND t.video_name = src.video_name)

  WHEN MATCHED THEN

    UPDATE SET 

      t.last_position = src.last_position,

      t.fully_watched = 'Y',--src.fully_watched,

      t.updated_on = SYSDATE

  WHEN NOT MATCHED THEN

    INSERT (username, video_name, last_position, fully_watched, updated_on)

    VALUES (src.username, src.video_name, src.last_position, src.fully_watched, SYSDATE);

  APEX_JSON.OPEN_OBJECT;

  APEX_JSON.WRITE('status','OK');

  APEX_JSON.CLOSE_OBJECT;

EXCEPTION

  WHEN OTHERS THEN

    APEX_JSON.OPEN_OBJECT;

    APEX_JSON.WRITE('status','ERROR');

    APEX_JSON.WRITE('message',SQLERRM);

    APEX_JSON.CLOSE_OBJECT;

END;

Step 8: 🎬 LOV-Based Video Listener

Add below code in Execute when Page Loads.

document.addEventListener('change', function(e) {
  if (e.target && e.target.id === 'P4_VIDEO_SELECT') {
    var fileName = e.target.value;
    var baseURL = 'https://your-sharepoint-url/';

Step 9: 🔄 Resume Logic

video.addEventListener('loadedmetadata', function() {
    loadResumeAndTrack(video, fileName);
    video.play();
});

Step 10: 📊 Real-Time Tracking

Instead of just time, the system tracks actual watched seconds:
watchedSeconds.add(cur);
var watchedPct = (watchedSeconds.size / dur) * 100;
var fullyWatched = watchedPct >= 95;
This ensures:
✔️ Users cannot skip ahead
✔️ True engagement is measured
Full code:
// Listen for changes in the LOV item
document.addEventListener('change', function(e) {
  if (e.target && e.target.id === 'P4_VIDEO_SELECT') {
    var fileName = e.target.value;
    var baseURL = 'https://your-sharepoint-url/';
    var video = document.getElementById('myVideo');
    var source = document.getElementById('videoSource');
    var pctEl = document.getElementById('progressPercent');
    var container = document.getElementById('videoContainer');

    // Hide everything if no video selected
    if (!fileName) {
      if (video) {
        video.pause();
        video.src = '';
        video.load();
      }
      if (container) container.style.display = 'none';
      if (pctEl) pctEl.textContent = '0%';
      $s('P4_LAST_POSITION', 0); // reset page item
      return;
    }

    // Show video container
    if (container) container.style.display = 'block';

    if (video && source) {
      // Remove previous listeners
      var newVideo = video.cloneNode(true);
      video.parentNode.replaceChild(newVideo, video);
      video = newVideo;
      source = document.getElementById('videoSource');
      source.src = baseURL + fileName;
      video.load();

      // Load resume position and start tracking
      video.addEventListener('loadedmetadata', function() {
        loadResumeAndTrack(video, fileName);
        video.play();
      });
    }
  }
});

// Function to handle resume, progress, and saving
function loadResumeAndTrack(video, videoName) {
  var pctEl = document.getElementById('progressPercent');
  if (!pctEl) {
    pctEl = document.createElement('span');
    pctEl.id = 'progressPercent';
    video.parentNode.insertBefore(pctEl, video.nextSibling);
  }

  function fmtTime(s) { 
    var m = Math.floor(s/60), sec = Math.floor(s%60); 
    return m + ':' + (sec < 10 ? '0' : '') + sec; 
  }

  var lastSaved = 0, saveInProgress = false;
  var watchedSeconds = new Set();

  function savePos(pos, fullyWatched) {
    if (saveInProgress) return;
    saveInProgress = true;
    apex.server.process('SAVE_VIDEO_POSITION', {
      x01: videoName,
      x02: String(pos),
      x03: fullyWatched ? 'Y' : 'N'
    }, {
      dataType: 'json',
      success: function() { lastSaved = pos; saveInProgress = false; },
      error: function() { saveInProgress = false; }
    });
  }

  // Resume playback from backend
  apex.server.process('GET_VIDEO_POSITION', { x01: videoName }, {
    dataType: 'json',
    success: function(d){
      var pos = Number(d.last_position) || 0;
      if (video.duration && pos > 0 && Math.abs(video.currentTime - pos) > 1)
        video.currentTime = Math.min(pos, video.duration - 0.5);

      // Set initial percentage in page item and progress bar
      var pct = ((video.currentTime / video.duration) * 100).toFixed(1);
      $s('P4_LAST_POSITION', pct);
      pctEl.textContent = fmtTime(video.currentTime) + ' / ' + fmtTime(video.duration) + ' (' + pct + '%)';
      var bar = document.getElementById('progressBarFill');
      if (bar) bar.style.width = pct + '%';
    }
  });

  // Update progress on video play
  video.addEventListener('timeupdate', function() {
    if (!video.duration) return;

    var cur = Math.floor(video.currentTime);
    var dur = Math.floor(video.duration);
    var pct = ((cur / dur) * 100).toFixed(1);

    // Update text
    pctEl.textContent = fmtTime(cur) + ' / ' + fmtTime(dur) + ' (' + pct + '%)';

    // Update progress bar
    var bar = document.getElementById('progressBarFill');
    if (bar) bar.style.width = pct + '%';

    // Update APEX page item live
    $s('P4_LAST_POSITION', pct);

    // Track watched seconds
    watchedSeconds.add(cur);
    var watchedPct = (watchedSeconds.size / dur) * 100;
    var fullyWatched = watchedPct >= 95;

    if (Math.abs(cur - lastSaved) >= 5 || fullyWatched) {
      savePos(cur, fullyWatched);
    }
  });

  // Save when paused, hidden, closed, or ended
  ['pause','visibilitychange','beforeunload','ended'].forEach(function(evt){
    window.addEventListener(evt, function(){ 
      if (!video.duration) return;
      var cur = video.currentTime, dur = video.duration;
      var watchedPct = (watchedSeconds.size / dur) * 100;
      var fullyWatched = watchedPct >= 95;
      savePos(cur, fullyWatched);
    });
  });
}

📈 Live Progress Visualization
A progress bar visually shows real-time viewing percentage.

💡 Enterprise Benefits

This solution provides:

✔️ Centralized video storage
✔️ Accurate engagement tracking
✔️ Resume playback capability
✔️ Skip prevention

🏆 Conclusion
By integrating SharePoint-hosted videos with Oracle APEX tracking capabilities and smart resume logic, 
this solution enables intelligent monitoring of user engagement without local video storage.

This approach ensures scalability, flexibility, and enterprise-level video learning management.