Learning Management System (LMS) in Oracle Apex

Creating a Hierarchical Video Lessons Viewer in Oracle APEX
✅ Embedded video playback
✅ Rich HTML content
✅ Click-to-load interaction

Step 1: Create the LESSONS Table



CREATE TABLE "LESSONS"
(
"LESSON_ID" NUMBER,
"TITLE" VARCHAR2(
"TITLE" VARCHAR2(200),
"VIDEO_URL" VARCHAR2(300),
"CONTENT" CLOB,
"LESSON_ORDER" NUMBER,
"DURATION_MIN" NUMBER,
"CREATED_AT" DATE DEFAULT SYSDATE,
"PARENT_LESSON_ID" NUMBER(10,0),
PRIMARY KEY ("LESSON_ID")
);

Step 2: Display Hierarchical Lesson Tree (Left Region)

SQL Query for the Left Region (Tree View):

SELECT
LPAD('> ', 2 * (LEVEL - 1)) || title AS indented_title,
lesson_id,
parent_lesson_id,
lesson_order,
level
FROM lessons
START WITH parent_lesson_id IS NULL
CONNECT BY PRIOR lesson_id = parent_lesson_id
ORDER SIBLINGS BY lesson_order;

Step 3: Create a Hidden Item
Create Hidden Item:

Step 4: Show Video and Content (Right Region)
SQL Query for the Right Region:

SELECT
'<iframe width="100%" height="500" src="' || video_url || '" frameborder="0" allowfullscreen></iframe>' ||
'<div style="margin-top: 20px;">' || content || '</div>' AS lesson_html
FROM lessons
WHERE lesson_id = :P138_LESSON_ID;

🖱 Step 5: Add Interactivity

In this tutorial, we will build a dynamic video lesson viewer using Oracle APEX that supports:

✅ Nested lesson hierarchy (like chapters and sub-chapters)

Let’s walk through the components step-by-step:

Use the following script to create a table to store lesson data, including video URL, content, hierarchy, and duration.

We use Oracle's powerful CONNECT BY query to create a hierarchical lesson viewer with indentation.

📌 This query ensures lessons are shown in a nested format, ordered by lesson_order.

In the APEX page (say, Page 138), create a hidden item to hold the selected lesson ID:

• Name: P138_LESSON_ID

• Type: Hidden

• Default: Null

You will set this item value when a user clicks on a lesson title.

This region will display the lesson content and video by using the selected lesson_id.

📺 This query dynamically shows the embedded video and formatted content.

✅ Tip: Set the region type to "HTML" so the embedded iframe renders properly.

You can make the tree region interactive using a Dynamic Action:

1. Event: Click on a row in the left region.

2. True Action: Set Value

   • Set P138_LESSON_ID from lesson_id of the selected row.

3. Refresh the Right Region after setting the value.

Table :  LESSONS :

CREATE TABLE  "LESSONS" 
   (	"LESSON_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"TITLE" VARCHAR2(200), 
	"VIDEO_URL" VARCHAR2(300), 
	"CONTENT" CLOB, 
	"LESSON_ORDER" NUMBER, 
	"DURATION_MIN" NUMBER, 
	"CREATED_AT" DATE DEFAULT SYSDATE, 
	"PARENT_LESSON_ID" NUMBER(10,0), 
	 PRIMARY KEY ("LESSON_ID")
  USING INDEX  ENABLE
   );

Region Left :

 SELECT 
  LPAD('> ', 2 * (LEVEL - 1)) || title AS indented_title,
  lesson_id,
  parent_lesson_id,
  lesson_order,
  level
FROM lessons
START WITH parent_lesson_id IS NULL
CONNECT BY PRIOR lesson_id = parent_lesson_id
ORDER SIBLINGS BY lesson_order;

Create a Hidden Item :    :P138_LESSON_ID

Region Right :

SELECT  '<iframe width="100%" height="500" src="' || video_url || '" frameborder="0" allowfullscreen></iframe>'||'<div style="margin-top: 20px;">' || content || '</div>' AS lesson_html FROM lessons WHERE lesson_id = :P138_LESSON_ID;

VIDEO URL  Data :

Oracle Database

> How to install Oracle Database 19C in Windows 10
https://www.youtube.com/embed/o045xTh7nSs?si=Sp2V5EES2SRdSu2v

> How to create TNS Service
https://www.youtube.com/embed/T_NYej4_TOA?si=--gP2a5hTZ3o8jC3

> How to Unlock User
https://www.youtube.com/embed/SAdfBia4jkQ?si=PB67pntPbGqsWF6G

Oracle Apex

> How to Install Oracle Apex 
https://www.youtube.com/embed/gI1my3NRdXE?si=UhskcvQsbn6h383v

> Oracle Apex Uninstall
https://www.youtube.com/embed/DtkRu-DOiEY?si=dWenbDii6vQqVm3o

> Change Port Number
https://www.youtube.com/embed/XLTVEiUGzmU?si=OCe3fyCD_I3TJyDt

select nvl(max(LESSON_ID),0)+1 INTO :P4_LESSON_ID FROM LESSONS;