Task App using PL/SQL Dynamic Content Region

Hello everyone and welcome back to our blog. In this post, we will walk you through an example in which we will show you how to use the "PL/SQL Dynamic Content" region with an example :).

But first, what is PL/SQL Dynamic Content region? well, it is a native apex region type based on PL/SQL that allows you to render any HTML or text using the PL/SQL Toolkit. So,  you use this type of region to generate dynamic HTML code based on some PL/SQL logic, think about it like PHP where you mix HTML with PHP.

Now, for more understanding let's demonstrate by using the dynamic content region in an example where we want to create a dynamic list of each developer's tasks. Suppose we have a group of developers and each developer has some tasks to do, we want to show these tasks and the status of the task whether it is completed or not. We will also give the user the ability to update the status of the task. So get ready, sign in to your development workspace, and let's start :).

First, you need to create two tables and add some data, you can use the following script and run it in your SQL workshop, navigate to the SQL script and run the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

CREATE TABLE "DEVELOPERS" ( "DEVELOPER_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "DEVELOPER_NAME" VARCHAR2(20) NOT NULL ENABLE, "DEVELOPER_EMAIL" VARCHAR2(50) NOT NULL ENABLE, CONSTRAINT "DEVELOPERS_PK" PRIMARY KEY ("DEVELOPER_ID") USING INDEX ENABLE ) / CREATE TABLE "TASKS" ( "TASK_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "TASK_TITLE" VARCHAR2(20) NOT NULL ENABLE, "TASK_DESCRIPTION" VARCHAR2(100), "DEVELOPER_ID" NUMBER, "COMPLETED" CHAR(1), CONSTRAINT "TASKS_PK" PRIMARY KEY ("TASK_ID") USING INDEX ENABLE ) / ALTER TABLE "TASKS" ADD CONSTRAINT "TASKS_FK" FOREIGN KEY ("DEVELOPER_ID") REFERENCES "DEVELOPERS" ("DEVELOPER_ID") ENABLE /

Now let's insert some data to play with:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

insert into developers (developer_name, developer_email) values ('wassim', 'wassim@mail.com'); insert into developers (developer_name, developer_email) values ('mohamad', 'mohamad@mail.com'); insert into developers (developer_name, developer_email) values ('amir', 'amir@mail.com'); insert into developers (developer_name, developer_email) values ('sami', 'sami@mail.com'); / insert into tasks (task_title, task_description, developer_id, completed) values ('Add icon to button', 'add icon to the submit button',1,'Y'); insert into tasks (task_title, task_description, developer_id, completed) values ('remove padding', 'remove the padding from the divs',1,'N'); insert into tasks (task_title, task_description, developer_id, completed) values ('add image', 'add image to the about page',1,'N'); insert into tasks (task_title, task_description, developer_id, completed) values ('load data', 'load data into the table',2,'N'); insert into tasks (task_title, task_description, developer_id, completed) values ('send email', 'send email to the users',3,'N'); insert into tasks (task_title, task_description, developer_id, completed) values ('Adjust background', 'the background color has to be changed',3,'N'); insert into tasks (task_title, task_description, developer_id, completed) values ('change the font', 'change the font size on home page',1,'Y'); /

Next, create a demo app and create an empty page, navigate to the page and add a region of type PL/SQL dynamic content, Now we are all set up and ready :). 

In the dynamic region add the following code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

DECLARE c_devs SYS_REFCURSOR; c_tasks SYS_REFCURSOR; v_dev_name developers.developer_name%TYPE; v_developer_id developers.developer_id%TYPE; v_task_title tasks.task_title%TYPE; v_task_id tasks.task_id%TYPE; v_completed tasks.completed%TYPE; v_tasks_cnt number; BEGIN OPEN c_devs FOR select d.developer_id, d.developer_name, cursor(select task_title, task_id, completed from tasks t where t.developer_id=d.developer_id group by task_title, task_id, completed ) c_tasks from developers d; HTP.P('<ul class="dev-ul">'); LOOP FETCH c_devs INTO v_developer_id, v_dev_name, c_tasks; EXIT WHEN c_devs%NOTFOUND; select count(t.task_id) into v_tasks_cnt from tasks t where t.developer_id=v_developer_id; HTP.P('<li class="dev-li"><h4>'||v_dev_name||' <small>'||v_tasks_cnt||' tasks</small></h4>'); HTP.P('<ul class="tasks-ul">'); LOOP FETCH c_tasks INTO v_task_title, v_task_id, v_completed; EXIT WHEN c_tasks%NOTFOUND; HTP.P('<li class="tasks-li">'||APEX_ITEM.CHECKBOX2(1,v_task_id, CASE WHEN v_completed ='Y' THEN 'CHECKED' ELSE NULL END) || ' <label data-content="'||v_task_title||'" for="'||v_task_id||'">' || v_task_title || '</label>' || '<span data-id='||v_task_id||' class="delete-task fa fa-trash"></span>' ||'</li>'); END LOOP; HTP.P('</ul>'); HTP.P('</li>'); CLOSE c_tasks; END LOOP; CLOSE c_devs; HTP.P('</ul>'); END;

In the first two lines, we declared two ref cursors, the first one we will use to point to a developer in the developer's table and the second to point to a task in the tasks table. From the developer's table, we need the developer name and developer id, so we declared two variables in lines 4,5 that have the same type as the column developer_name/id in the table, in the same way, we created the v_task_title, v_task_id, v_completed variables which all are variables to hold the task's attributes values. 

In line 12 we are executing the query that is associated with the c_dev cursor, and as you see in line 16 we have a nested cursor for retrieving the tasks associated with that particular developer. After that we will loop through the developer list and fetch a single record, for each record we will then loop again to fetch its tasks (as you see in the nested loop, lines 29 to 57). The inner loop will be exited as soon as no tasks are found for the developer, then the outer loop will fetch the next developer, and so on until no more developers are found. 

In lines 29, 37, 38, 44-49, 52, 53, and 61 we are generating HTML tags to be displayed in our document. The template of the HTML would be like the following: 

1 2 3 4 5 6 7

<ul class="dev-ul"> <li class="dev-li"><h4>#Developer Name#<small> #of tasks </small></h4> <ul class="task-ul"> <li class="task-li">CHECKBOX<label data-content="#task title#">#Task Title#</label><span class="delete-task fa fa-trash">TRASH</span></li> </ul> </li> </ul>

Let's add some CSS to make it looks better, add the following CSS to the in-line CSS on the page level

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107

* { box-sizing: border-box; } *::before, *::after { box-sizing: border-box; } .dev-ul{ list-style: none; } .delete-task{ color: red; margin-left: auto; font-size: 1.3rem; cursor: pointer; } .dev-li h4{ padding: 10px; margin: 5px; background-color: #b3e5fc; border-left: 5px solid #4fc3f7; cursor: default; } .dev-li h4:hover{ background-color: #4fc3f7; transition: background-color 400ms linear; } .tasks-ul{ list-style: none; margin-right: 5px; margin-top: 0px; display: none; } .tasks-li{ padding: 10px; border: 1px solid #eee; background-color: #eee; margin-bottom: 2px; display: flex; flex-direction: row; align-items: center; align-content: center; flex-wrap: nowrap; } .tasks-li:hover{ background-color: #ccc; cursor: default; transition: background-color 400ms linear; } .tasks-li [type="checkbox"]{ position: relative; width: 1.5em; height: 1.5em; border: 1px solid #bdc1c6; border-radius: 4px; appearance: none; outline: 0; cursor: pointer; transition: background 175ms cubic-bezier(0.1, 0.1, 0.25, 1); } .tasks-li [type="checkbox"]::before { position: absolute; content: ''; display: block; top: 1px; left: 6px; width: 8px; height: 14px; border-style: solid; border-color: #fff; border-width: 0 2px 2px 0; transform: rotate(45deg); opacity: 0; } .tasks-li [type="checkbox"]:checked { color: #fff; border-color: #06842c; background: #06842c; } .tasks-li [type="checkbox"]:checked::before { opacity: 1; } .tasks-li [type="checkbox"]:checked ~ label::before { clip-path: polygon(0 0, 100% 0, 100% 100%, 0 100%); } .tasks-li label { position: relative; cursor: pointer; font-size: 1.1em; font-weight: 400; padding: 0 0.25em 0; user-select: none; } .tasks-li label::before { position: absolute; content: attr(data-content); color: #9c9e9f; clip-path: polygon(0 0, 0 0, 0% 100%, 0 100%); text-decoration: line-through; text-decoration-thickness: 3px; text-decoration-color: #363839; transition: clip-path 200ms cubic-bezier(0.25, 0.46, 0.45, 0.94); }

This just shows the developers and the assigned tasks, we need to make it a little interactive by allowing the user to delete or update a task, we already added the HTML items and CSS classes that we require, we only have to add some Javascript code and a process to handle the request. On the page level add the following Javascript to execute when the page loads:

1 2 3 4 5 6 7 8 9 10 11 12 13

$('.delete-task').on('click',function(){ apex.submit({request:'DELETE', validate:false, set:{"P3_DELETE_ID": $(this).data('id')} }); }); $('.dev-li').on('click', function(e){ $(this).children('ul').toggle(); }); $('.tasks-ul').on('click', function(e){ e.stopPropagation(); });

In lines 1-5 we are submitting the page whenever a user clicks on the Trash icon, we are setting the request variable to delete and we are preventing the page items from being validated, the last attribute will set a page item called P3_DELETE_ID (which we have to create it in our page as a hidden item) to the current task id, so we retrieve the ID of the task where the user has clicked from the data HTML attribute. On the server-side, we need a process to handle this, go to processes, and create a new process that runs processing, name it "Delete task", and add the following PL/SQL. Make sure to set the server-side condition type to "request=value" and the value should be "DELETE":

1 2 3 4 5

BEGIN delete from tasks WHERE task_id = :P3_DELETE_ID; END;

For updating a task, we only need a button that submits the page and we need to create a server process to handle the update request. Add a button to our region and name it "Update", this button will simply submit the page, then add a new process that runs on processing and executes the following PL/SQL code:

1 2 3 4 5 6 7 8 9

BEGIN UPDATE tasks set completed='N'; FOR I in 1..APEX_APPLICATION.G_F01.COUNT LOOP UPDATE tasks SET completed='Y' WHERE task_id = to_number(APEX_APPLICATION.G_F01(I)); END LOOP; END;

Now, we require a form to create a new task, so let's do it...

On the same page, add a new form, and for source select SQL

Add the following SQL:

1 2 3 4 5 6

select TASK_ID, t.TASK_TITLE, t.TASK_DESCRIPTION, d.developer_id, t.COMPLETED from TASKS t inner join developers d on t.developer_id = d.developer_id

Change the developer id column to select list, since we want to display the developer name, in the source add this SQL:

1 2 3

select d.developer_name display_value, d.developer_id return_value from developers d;

Add a new button to the form to submit the page and perform an insert action into the database

And finally, add two processes, one for performing a DML and the second one to clear the session items

at the end you should see something like:

And that's it :) we just have created a dynamic PL/SQL region and added some functionality. 

We hope you found this post useful :).

Demo