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: , ,

posted by Mohamad Bouchi @ February 21, 2022   2 Comments

Monday, February 14, 2022

Javascript API (#4 Message namespace)

Hello everyone and welcome again here in our Blog. In this post, we will show you how to use the message namespace, and as always, with a practical example.

The message namespace is one of the native apex namespaces provided to you. This namespace is used to handle client-side display and management of messages, such as error and success messages. 

When to use the message namespace?

you use this namespace when you want to show some messages to the users. A very common use-case is validation, for example, you have a required text field and you want to alert the user if he leaves it empty, or if the text field must be in Email format. You can also use the namespace to show a success message after a user successfully complete a process. These are some use-cases where you can benefit from the message namespace.

Message types

The namespace has two message types, ERROR, and SUCCESS. You can use the property TYPE to specify the type of message you want as we will see later in this post. 

Functions

The namespace provides some functions that you can use to perform some tasks. Below we will take a look at some of these functions.

alert()

The first function we will talk about is alert(), which is used to display the alert dialog with a message passed as a parameter and an Ok button, you can also provide a callback function that will be fired after the user responds to this alert. The dialog displays using the jQuery UI ‘Dialog’ widget and there are some differences between this function and a browser’s built-in alert function:
  • The dialog style is consistent with the rest of the app.
  • The dialog can be moved.
  • The call to apex.message.alert does not block. Any code defined following the call to apex.message.alert will run before the user presses OK. Therefore code to run after the user closes the dialog must be done from within the callback.
Now let's have an example, go to our Demo app or create a new application if you don't already have one, navigate to the first page and create a new region with a button and let's name the button "show alert", now add a dynamic action to that button and set the true action to Javascript (please note that there is already an alert action provided to you, but in this series, we are the Javascript APIs) add the following code and run the application
apex.message.alert("some alert text", ()=>{
console.log("This runs after the dialog is closed");
});
Once the user clicks the show alert button, the dialog shows up with the message and an ok button. The user has to press the ok button to close the dialog, once he has done that, the callback function will be executed.

confirm()

Similar to the alert function, but here, the dialog has a cancel button beside the ok button. In this function, the callback function accepts a parameter that tells you whether the user pressed the Ok button or the cancel button, so you can perform different actions depending on the user input. This is useful when you want to warn the user before deleting something for example. 
Go back to the demo app, add a new button "Show Confirm", add a dynamic action to the button and add a true action that runs the following code
apex.message.confirm("some text", (okPressed)=>{
if (okPressed)
console.log("The user pressed the ok Button");
else
console.log("The user pressed the Cancel Button");
});
Run the application and open the console to see the output. 

showPageSuccess()

You can use this function to show the user a success message with a specific message, for example, when a user successfully filled out a form or to show that a transaction was successfully saved. 
Go to the demo app, add a new button "ShowSuccessMessage", add a dynamic action and change the true action to execute Javascript code then add the following code
apex.message.showPageSuccess( "Changes saved!" );
Run the application and try it! 

hidePageSuccess()

As the name suggests, this function hides the page success notification. After you display a success message, it won't disappear until you hide it, one way to do that is using this function. 
Go back to the previously created dynamic action that shows the success message and change the code to the following
apex.message.showPageSuccess( "Changes saved!" );
setTimeout(()=>{
apex.message.hidePageSuccess();
}, 1000);
This will set a timeout and the hide-message will be executed 1 second after it is displayed.

clearErrors()

This function clears any errors currently displayed on the page, we will use this function later in this post when we talk about showErrors() function

showErrors()

This function is used to display error messages to the user, when they enter a wrong value in an input text item or when something went wrong while saving a form. You can show the error message on a page level which means all error messages will be shown on the right corner of the page and the style will be inherited from the page notification template, or you can show the error message next to the item where the error occurred, in this case, the style will be driven from the item label template.  You don't need to worry about the style and the markup, you just have to specify the location where the notification will be displayed. The function accepts an object or an array of objects, in each object you can specify the message attributes e.g. location, message text, and so on. 
Go to the demo app, add a text item, give it the name "P1_ERROR_MESSAGE" and a button "ShowError"
Then add a dynamic action to the button, change the true action to execute Javascript code, and write the following code
apex.message.clearErrors();
apex.message.showErrors([
{
type: "error",
location: [ "page", "inline" ],
pageItem: "P1_ERROR_MESSAGE",
message: "Name is required!",
unsafe: false
},
{
type: "error",
location: "page",
message: "Page error has occurred!",
unsafe: false
}
]);
The first line in the code clears the error stack, in case the user had previous errors and, the second line adds two errors to the error stack and shows them. 
The first error in the array is an object, the type of the notification is an error and in the location attribute we want to show the error message on the page level and also next to the item, so in the pageItem attribute we should tell which page item is associated with this error message, the next attribute is the message we want to show and finally, the last attribute tells the function whether to escape the message text or not, in this case, the message text is considered as a safe message so it won't be escaped. 
the next error object is a page-level error and therefore we don't need to specify a page item. 
Run the application and you should see something like this

setThemeHooks()

If you want to change the default behavior of the notification messages or if you want to override the style then you need to use this function. It allows you to run a specific code before the show page notification functionality, and also before the hiding page notification functionality. Let's say for example you want the success messages to disappear 3 seconds after displaying, but if the message type is an error then you don't want it to disappear automatically. 
Go to our page where all items are and in the "Execute when page loads" write the following code (It is better to add this code in template initialization code, but for this demo is ok)
apex.message.setThemeHooks({
beforeShow: function( pMsgType, pElement$ ){
if ( pMsgType === apex.message.TYPE.SUCCESS ) {
setTimeout(function() {
$('.t-Alert').fadeOut('slow');
}, 2000);
}
},
beforeHide: function( pMsgType, pElement$ ){
console.log("This is before hide!");
}
});
The pMsgType parameter identifies the message type (as we said before, there are two types Error and Success), and the pElement$ parameter is the JQuery object that contains the element to being shown (the notification element, in our case, would be an HTML span element).
Go to the show success dynamic action and comment the code that hides the success message
Run the application and try it (you need to open the console to see the beforeHide message). 

So that's it in this post, we hope that you learned something :).


References:
  • https://docs.oracle.com/en/database/oracle/application-express/21.2/aexjs/apex.message.html



Labels: , ,

posted by Mohamad Bouchi @ February 14, 2022   1 Comments

Tuesday, February 8, 2022

Summary APEX Mail (APEX Mail #10)

   




Everything you need to know about sending emails with Oracle APEX has been described in the previous blogs. At the end of this series we would like to give a summary and conclusion of this. So let's move on to the last article in our series.


#10 Summary APEX Mail

APEX offers almost everything out-of-the-box that you need to send high quality emails easily. A lot can be implemented simply and declaratively. Here you will find once again all important topics we have listed.


1. Sending emails with "E-Mail Templates"

We have started the series with sending an e-mail template without having to write any further program code for it. Since APEX version 21.2 it is easier than ever to send emails with templates. Everything can be configured declaratively in APEX Builder. In this blogpost we have described how to send an email without writing any additional code. Read here how it works






2. Sending emails with the "APEX_MAIL" API

In the second blog post we described how easy and simple it is to send email templates with a few lines of PL/SQL code even in an APEX version older than 21.2. Read here how we described it and discover the used "APEX_MAIL" API.






3. Preview of your email

Next, we described how to preview the email before sending it. This can be very handy, especially if the content of the email is dynamic. Read here how we described it.







4. Sending emails with attachment

Important and often needed is also adding attachments to an email. Accordingly, we have of course included this in our series and described it. Read here what options you have and how it works.






5. Embedding images in an email

In the 5th article we described how to embed or display images in an email. As so often, there are several options. Especially APEX version 21.2 makes life much easier for us developers. Read here which possibilities we have described.






6. Display tabular data in an email

Next, we showed you how to display tabular data in an email template. Surely there are several approaches, but with the one described here we have already achieved a good goal.Read how we did it here.






7. Checking the mail log and queue

In the 7th blog post we have listed how you can check which emails are in the queue, which have been sent successfully or if an error occurred during sending. Monitoring is very important and should not be neglected. Read here how you can do that.






8. Including a link in an email to an APEX App

Next, we showed you how to insert a hyperlink in an email that redirects you to an APEX application. It's not that tricky, but there are a few details to keep in mind. So read here how we described it.






9. Advanced email techniques

To wrap up this series, we've listed a few more techniques, tips and tricks, do's and don'ts, or what we struggled with. Maybe it will help you in creating your own email templates. So give it a try and read some final words here.







Conclusion

In conclusion, we can say that APEX makes it very easy for developers to send well-structured emails. Pretty much everything you need is provided out of the box and most of it can be developed with little to no code. According to the "lowcode" motto. Of course, a few details should be considered here as well. Will the recipient read their email on mobile phone? Which email clients do I write to? Should images be displayed? There are answers for everything and APEX will do a lot for us. Our impression is that APEX has done an excellent job here, so that we can and should send emails without hesitation.

So try it out and let us know if we forgot to describe something or if you have any hints for us.


Finally here is the link for our demo app. 
There you can also download a copy of the demo app ;-)


Happy APEXing!!!

Labels: , ,

posted by Timo @ February 08, 2022   2 Comments