Tuesday, October 11, 2022

Using Template Directives in E-Mail Templates (APEX Mail #11)

 




We have described the most important things about "Emails with Oracle APEX" in the previous blogs. However, new features are added in new releases and so we would like to continue the series to describe them as well.

#11 Using Template Directives in E-Mail Templates

In this blog post we would like to describe the Template Directives feature introduced in APEX 22.1. 
Template directives are supported only by specified attributes of cards, interactive grids and email templates. These directives are processed as part of client-side substitutions. Template Directives allow you to apply client-side directives to control the display and formatting of data returned by SQL. 

The following directives can be applied

  • If Condition Directives (if, else, elsif, endif)
  • Case Condition Directives (case, when, otherwise, endcase)
  • Loop Directives (loop, endloop)

Why and when to use template directives

  • Clean Code! Keep your SQL as simple as possible
  • Security! No RAW Syntax needed, so the risk of cross-site scripting will be reduced
  • Performance! Reduce the size of SQL results

Let´s start by creating a demo app

Create a new application in the App Builder. In the wizard you can name your application "Email Demo #11" for example. Our application is now ready to add more components. The home page has no regions at the moment.

Create an Email Template

Let´s create an Email Template first. To do this, go to "Email Templates" under "User Interface" of the Shared Components section.

For our newly created template, we enter the following information:

Template Name: Hello Employee
Static Identifier (will be generated automatically): HELLO_EMPLOYEE
Email Subject: Hello Employee

Then we can populate the HTML snippets for our template. 

Header:

1
<H1>Hello Employee!</H1>


Body:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<p>Hello #NAME#,</p>

{case JOB/}
{when SALESMAN/}
<p>your salary is <strong>#SAL#</strong> and your commission is <strong>#COMM#</strong>.</p>
{when PRESIDENT/}
<p>your salary is TOP SECRET!</p>
{otherwise/}
<p>your salary is <strong>#SAL#</strong>.</p>
{endcase/}

<p>Note: This is an email sent from <strong>Oracle APEX</strong>.</p>

<p>Best Regards</p>


From Line 3 to 10 we will see the Template Directive. 
In this example we use the "Case Condition Directives"! 

The {case/} directive compares the job title from the SQL statement and returns a combination of "SAL" and "COMM" should the job title be "SALESMAN". If the job title is "PRESIDENT" then nothing is returned and for all other comparisons only "SAL" will be returned.


And this is how it looks in the APEX Builder:




By clicking on "Create Email Template" we have created the template and can now use it in our application.


Create the Home-Page

Now our application is ready to add a region, some components and a process to send emails.

First, add a "Static Content" region to your "Body" and name it for example "Send Email".
Then add a "Text Field" Item to this region, name it "P1_TO" and select "Email" as the subtype. This new Item will have the address of the email recipient.

Finally, the page needs a button to send an email. So add a button "Text and Icon (Hot)" to the "Close" position. Name the button "Send" and use e.g. "fa-send" as the icon and "Send email" as the label.

When you start the application now, it should look like this.



Create a process to send the email

Next, we need a process to send an email when we hit the "Send Email" button.

Therefore, switch to Processes in the tree structure and create a new process. For example, name it "Send mail" and select the type "Execute Code". 

Under Source, enter the following PL/SQL 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
begin
    for rec in (
        select ename
             , job
             , sal
             , comm
          from emp
    )
    loop      
        apex_mail.send (
        p_to                 => :P1_TO, 
        p_from               => :APP_EMAIL, 
        p_template_static_id => 'HELLO_EMPLOYEE',
        p_placeholders       => '{' ||
        '    "NAME":'            || apex_json.stringify( rec.ename ) ||
        '   ,"JOB":'             || apex_json.stringify( rec.job ) ||
        '   ,"SAL":'             || apex_json.stringify( rec.sal ) ||
        '   ,"COMM":'            || apex_json.stringify( rec.comm ) ||
        '}' );
     end loop;
 
     apex_mail.push_queue;
exception
     when others then
         apex_debug.error('Critical error %s', sqlerrm);
     raise; 
end;

Note: The Sample Dataset "Emp/Dept" must be installed for this example.


Finally, enter the success and error message and the server-side condition when the process should be executed.

Success Message: Emails sent
Error Message: Emails sent failed!
When Button Pressed: Send


That's it. Let's start the application again and try to send emails. Enter your email adress in the "To" field and click on the "Send email" button. Then you will receive an email for each employee from the EMP table.


Here are some examples of what email results should look like.


Example: Salesman




Example: President




Example: Manager




As you can see, the text in each email is different depending on the job title. And this is exactly what we have achieved with the new Template Directive feature! No complex buisness logic in our SQL-Code. Good performance and no security issues :)


So, that was a simple example...feel free to look at the if or loop directives and what you can do with them ;)


Here is the link for the demo app.


Labels: ,

Saturday, June 11, 2022

Docker-compose for Oracle APEX

 
Hello everyone and welcome back to our blog...


In the previous post, we showed you how to run an Oracle XE instance in a docker container and then run ORDS in another container to use it to connect to APEX. 

We first downloaded the Images we needed, then we had to write some codes in the console/Terminal to configure the networks and volumes and for each time we wanted to spin up a DB container or an ORDS container, we had to write a docker-console command and pass all the parameters. 

There is an easier way that we can use to run all the containers with the configurations in just one command, and that tool is docker-compose. In this post, we will show you how to use docker-compose and how to run APEX in docker just as we did last time.

In docker-compose, we use a YAML file to define the services we want to run. By services we mean containers, so each container is represented as a service in docker-compose. Under each service, we can list all the configurations that are related to that service. One advantage of using docker-compose is to make your commands structured, easy to read, and maintain or change. For example, see how the command for running a docker container looks like we you want to write it in the terminal:

    1
    2
    3
    4
    5
    6
    docker run -d --name db-container\
    >          -p 1521:1521\
    >          -e ORACLE_PWD=1230123\
    >          -v db-demo-volume:/opt/oracle/oradata\
    >          --network=demo-network\
    >          --hostname database
    >          oracle-xe-21.3

    It is not easy to read right :). Now let's see how docker-compose will help us make this more efficient. 

    For using docker-compose we just need to create a YAML file wherever we want, so let's create a folder on the desktop and name it demo. In that folder create a file with the name docker-compose.yml

    Oracle XE Service

    We will first define the Oracle XE service with its related configurations in our compose file:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    version: '3.9'
    
    services:
      devdb:
        container_name: devdb
        image: container-registry.oracle.com/database/express:latest
        ports: 
          - 1521:1521
        environment:
          - ORACLE_PWD=1230123
        volumes:
          - dev-vol:/opt/oracle/oradata
        hostname: oracledev
    

    Keep in mind that the first line of every docker-compose file is for specifying the Compose file format version. In our example, we are using version 3.9.

    • The third line is the services we want this file to run, by services we mean containers. Under the services, we will list the containers we want to run. 
    • devdb is the name for our first service (container), the name can be anything but you better give meaningful names. Under this service, we will list the configurations related to it.
    • container_name This is equivalent to the --name tag when using the terminal. 
    • image is the image to be used for spinning up the container 
    • ports to map the ports between the container and the host. The first port is the host port and the second specifies the container port. This is equivalent to -p.
    • environment under this we pass the environment variable to the container, in our example, we are passing the oracle_pwd. This is equivalent to -e (You can create an env file and save all environment variables in it).
    • volumes to specify the paths to mount on the host and the container. The first path is the host and the second is the containers' path. This is equivalent to -v (At the end of the file we will define our volumes). 
    • hostname giving a name to the DB host machine. This is equivalent to --hostname.
    As you see this looks much cleaner than before :). 

    ORDS Service

    We will continue by defining the next service in the document. But before we do that, we need the connection string to tell ORDS how to connect to the database. So, we will create a new folder where the compose file is located, name the folder variables and inside the folder create text file conn_string and write the following in it.
    CONN_STRING=sys/1230123@oracledev:1521/XEPDB1
    Now back to the compose file, under the services we continue by adding the following:

    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    ords:
        container_name: ords
        restart: always
        depends_on:
          - devdb
        volumes:
          - ./variables:/opt/oracle/variables
        ports:
          - 8181:8181
        image: container-registry.oracle.com/database/ords:latest
    

    As you see, it is almost the same as the first service, we have changed the name of the service and container, ports, volume, and the image to use. We just added three lines 16, 17, and 18.

    • restart this will restart the service every time it fails. The database will take some time to be ready to use, in this time ORDS will try to connect to the database and it will fail, so we are retrying to connect every time until the connection is established.
    • depends_on we don't want ORDS service to start before the database service starts. So we are telling the compose that this service has to start after the devdb service. 

    Network and Volumes

    Now we will define the Networks and Volumes that we want to use. As we saw in line 12 of the compose file, we are mounting dev-vol volume to the path in the database container where the data are saved. In this way, we are saving the data on the host machine to prevent losing it when the container gets deleted. We will define this volume and give it a meaningful name, so we don't delete it later by mistake. Then we will define the network where the services will live, this is to make the communication between the services possible. Please note, if you use docker-compose, it will take care of the network configuration but it will give a random name, what we are doing is just giving a name to the network.
    In the same compose file add the following:

    24
    25
    26
    27
    28
    29
    30
    volumes:
      dev-vol:
        name: db-vol
        external: false
    networks:
      default:
        name: demo-network
    

    Final Compose file

    In the end, you should have a docker-compose file that looks like this:

     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
    version: '3.9'
    
    services:
      devdb:
        container_name: devdb
        image: container-registry.oracle.com/database/express:latest
        ports: 
          - 1521:1521
        environment:
          - ORACLE_PWD=1230123
        volumes:
          - dev-vol:/opt/oracle/oradata
        hostname: oracledev
      ords:
        container_name: ords
        restart: always
        depends_on:
          - devdb
        volumes:
          - ./variables:/opt/oracle/variables
    ports: - 8282:8181 image: container-registry.oracle.com/database/ords:latest volumes: dev-vol: name: db-vol external: false networks: default: name: demo-network

    Keep in mind, that the indentation in the docker-compose file is important.

    Docker-compose commands

    Up

    After we have done with the compose file, it is time to run it. Open a command line in the same folder where the compose file is located and write the following:

    1
    docker-compose up  
    

    This command will take care of running the containers, mapping the ports, and configuring the volumes and networks. You will use this command every time you want to run the containers.

    Down

    This command will stop the running containers, removing them and remove the networks associated, but it will keep the volumes:

    1
    docker-compose down       

    Stop

    This command will stop the running containers without removing anything:

    1
    docker-compose stop       

    P.s: If you don't have the images locally, you have to log in to the oracle container registry before running the Up command. Execute the following command in the Terminal and give your credentials

    1
    docker login container-registry.oracle.com       


    So that's it for docker-compose, we hope you found this useful :).

    References:
    • https://docs.docker.com/compose/

    Labels: , , , ,

    Tuesday, June 7, 2022

    Running APEX in Docker container


    Hello everyone and welcome back to our blog...


    In this blog, we are going to show you how to install Oracle XE in a Docker container, then install ORDS and APEX. It is recommended that you have a basic knowledge of Docker and how it works.

    First of all, you need to install docker on your machine, you can easily download and install it from the docker homepage.

    Then you will need to sign up for a free account on the oracle container registry, from this registry we will pull all the docker images we will need later.

    After you have Docker installed and signed up, you can follow throw with us :).

    Starting Docker

    After you installed Docker, you need to start docker desktop, to be able to use the docker commands. 

    Docker Network

    We will configure a docker network, to allow communication between the DB and ORDS container. For creating a network, we simply run the following command in the Terminal or Command line:

    1
    docker network create demo-network
     
    Now, to list the networks you have, run the following command:

    1
    docker network ls

    Docker Volume

    When we run our DB in a docker container all data changes are saved in the container, meaning we will lose all the data when the container gets deleted. To store the data independent from the container we can attach a volume. In this case, the data is saved on the host machine and will not be affected if we remove the container. We can later spin up a new container and mount the same volume to it.
    To create a volume we run the following command:

    1
    docker volume create db-demo-volume

    To list all the volumes, run the following:

    1
    docker volume ls
     

    Download and Build Oracle XE

    After installing docker, we need to download the Oracle XE docker-image. Afterward, we can spin up a container from that image, and our DB instance is ready to use. 

    In most cases, you will find the Docker image you need on Docker-Hub. In some cases, the image provider hosts the images on its registry, like in our example, the images are hosted on an Oracle registry.

    To pull the Oracle XE image, sign in to the Oracle registry using the account you created. You can do that by running the following command:

    1
    docker login container-registry.oracle.com
    

    After a successful login, you will see the following message


    Then we run the following command to pull the latest Oracle XE image (As of today 21c 21.3.0):

    1
    docker pull container-registry.oracle.com/database/express:latest


    To make things easy we will tag this image and give it a short name (oracle-xe-21.3):

    1
    docker image tag container-registry.oracle.com/database/express:latest oracle-xe-21.3

    1
    docker rmi container-registry.oracle.com/database/express:latest

    You can list the images to see what images you have:

    1
    docker images


    Now we can spin up a container from that image by running the following command:

    1
    2
    3
    4
    5
    6
    docker run -d --name db-container\
    >          -p 1521:1521\
    >          -e ORACLE_PWD=1230123\
    >          -v db-demo-volume:/opt/oracle/oradata\
    >          --network=demo-network\
    >          --hostname database
    >          oracle-xe-21.3
    • The first flag -d will run the container in a detached mode.
    • The parameter --name specifies the container name.
    • -p maps the port 1521 on the host machine to the port 1521 in the container, so we can connect to the database.
    • With -e we can pass environment variables. In this case, ORACLE_PWD set the password for the SYY, SYSTEM, and PDB_ADMIN users.
    • -v mounts the volume we created for the oradata files in the container.
    • --network connects the container to the network we created.
    • --hostname give a name to the DB server. 
    • The last parameter is the image we want to use to spin up the container.
    After the command finishes, you should see this message:


    Now if you go to SQL Developer, you can connect to the Database just like shown below:


    The password in our example is 1230123.

    Download and build ORDS and APEX

    Now we will pull the ORDS image from the Oracle registry, to do that run the following command:

    1

    docker pull container-registry.oracle.com/database/ords:latest


    We will tag the image to make the name shorter:

    1
    docker image tag container-registry.oracle.com/database/ords:latest ords-21.4

    1

    docker rmi container-registry.oracle.com/database/ords:latest



    For ORDS to connect to the database, it has to know the hostname where the DB is running, the password to use when connecting, and the service name. So, we will create a file on our host machine and save the connection string variable in it. When we spin up an ORDS container, we will mount this file to the container, and ORDS will use the connection string variable to connect to the DB. In this demo, we will create a folder on the desktop and name it ORDS, create a file in the ORDS folder and name it conn_string, in the file, we will store the connection string variable.


    1
    echo 'CONN_STRING=sys/1230123@database:1521/XEPDB1'>conn_string.txt

    Please note that the connection string variable has to be in the following format:
    conn_string=username/password@hostname:port/service
    The hostname of our DB is database, which we gave it as we initialized the container and the password is 1230123.

    For Windows Users, you need to remove the quote signs from the conn_string variable. Create a file and write the connection string variable like this:

    CONN_STRING=sys/1230123@database:1521/XEPDB1

    Now, we spin up an ORDS container by running the following command (You have to modify the 4th line so you point to where you saved the connection string file):

    1
    2
    3
    4
    5
    docker run -d --name ords 
    --network=demo-network 
    -p 8181:8181 
    -v /Users/mohamadbouchi/Desktop/ords:/opt/oracle/variables 
    ords-21.4
    

    If you are using Windows then the command line then the command would be:

    1
    2
    3
    4
    5
    docker run -d --name ords 
    --network=demo-network 
    -p 8181:8181 
    -v C:\Users\mohamadbouchi\Desktop\ords:/opt/oracle/variables 
    ords-21.4
    • The first flag -d will run the container in a detached mode.
    • The parameter --name specifies the container name.
    • -p maps the port 8181 on the host machine to the port 8181 in the container, so we can connect to APEX.
    • -v moves the connection string file to /opt/oracle/variables in the container.
    • --network connects the container to the network we created.
    • The last parameter is the image we want to use to spin up the container.
    Please notice, that we are attaching our network to the ORDS container, so both the DB container and ORDS container are running in the same network. And also we are mounting the folder where our connection string is stored to the container.
    This will take a few minutes because it will install APEX in the DB, you can monitor the installation by running the following command in a new Console/Terminal window:

    1
    docker exec -it ords tail -f /tmp/install_container.log
    

    When the APEX installation finishes,  you can open APEX in your browser using the following link:
    localhost:8181/ords/


    Use the following credentials to connect to the Internal Workspace:
    • Workspace: Internal
    • Username: Admin
    • Password: Welcome_1

    Removing the Containers

    After you finish, you may want to stop or remove the containers to free up system resources.
    To stop the container you can simply run the following command

    1
    docker stop <container name>

    At a later point you can start the container again with:

    1
    docker start <container name>

    You can remove the container completely by running

    1
    docker rm <container name>

    Since we are using volumes, our data will remain and we can mount it on different containers, or you can delete the volume, you can do that by running

    1
    docker volume rm <volume name>

    Finally to remove the network

    1
    docker network rm <network name>


    We hope you found this post useful :).

    References:

    Labels: , ,

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