Monday, February 21, 2022

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




Labels: , ,

2 Comments:

At March 23, 2022 at 5:36 PM , Blogger joseluisbarra said...

hello
thank you for sharing your knowledge.
I'm using the free oracle apex

https://apex.oracle.com/pls/apex/....


but I have to say , it didn't work, I receive this error


No Primary Key item has been defined for form region region create rask.
Contact your application administrator.

Technical Info (only visible for developers)
is_internal_error: true
apex_error_code: WWV_FLOW_FORM_REGION.NO_PRIMARY_KEY_ITEM
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 50420675253438397607
component.name: Initialize form using plsql dynamic
error_backtrace:
----- PL/SQL Call Stack -----
object line object
handle number name
0x397cf415c0 976 package body APEX_210200.WWV_FLOW_ERROR.INTERNAL_GET_ERROR
0x397cf415c0 1044 package body APEX_210200.WWV_FLOW_ERROR.INTERNAL_ADD_ERROR
0x397cf415c0 1517 package body APEX_210200.WWV_FLOW_ERROR.RAISE_INTERNAL_ERROR
0x3abb61d848 403 package body APEX_210200.WWV_FLOW_FORM_REGION.GET_REGION_PROCESS_META_DATA
0x3abb61d848 1950 package body APEX_210200.WWV_FLOW_FORM_REGION.INIT_PROCESS_INT
0x3abb61d848 2318 package body APEX_210200.WWV_FLOW_FORM_REGION.INIT_PROCESS
0x3abb6a9a58 1246 package body APEX_210200.WWV_FLOW_PROCESS_NATIVE.EXECUTE_PROCESS
0x3abdaf8440 3044 package body APEX_210200.WWV_FLOW_PLUGIN.EXECUTE_PROCESS
0x3abdad5260 167 package body APEX_210200.WWV_FLOW_PROCESS.PERFORM_PROCESS
0x3abdad5260 414 package body APEX_210200.WWV_FLOW_PROCESS.PERFORM
0x397c3fe640 3239 package body APEX_210200.WWV_FLOW.SHOW.RUN_BEFORE_HEADER_CODE
0x397c3fe640 3284 package body APEX_210200.WWV_FLOW.SHOW
0x397c3fe640 5369 package body APEX_210200.WWV_FLOW.RESOLVE_FRIENDLY_URL
0x399c912c48 4 anonymous block
Developer Toolbar



if you can guide me how to solve it, I'll appreciate it
(I searched some solutions but it didn't work what I tried)

thank you

 
At December 14, 2022 at 9:02 PM , Blogger HD said...

It worked perfectly

 

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to Post Comments [Atom]

<< Home