Enhance Page Process Security in Oracle APEX

 Secured application is highly recommended in every business. How we can make our application secure? There are many ways to secure an application. Today I'm going to discuss a way to secure page process. I assume, you have a complete functionsl page in your application. I choose page 47 in my APEX application to illustrate the process. You can choose your suitable page to implement this process. For visual assistance, you can follow this tutorial.

Now I'm going to discuss this process step by step-

STEP 1: Select page and open "Header Text" code Editor. Paste the below code here-

<div id="pageSpinnerOverlay">

<div class="spinner"></div>

<div class="msg"></div>

</div>

STEP 2: Open CSS > Inline code editor and paste the below css here-

/*Display the overlay block*/

#pageSpinnerOverlay {

display: none;

position: fixed;

top: 0;

left: 0;

width: 100%;

height: 100%;

background-color: rgba(0, 0, 0, 0.4);

z-index: 9999;

}

#pageSpinnerOverlay .spinner {

position: absolute;

top: 50%;

left: 50%;

transform: translate(-50%, -50%);

border: 8px solid #f3f3f3;

border-radius: 50%;

width: 60px;

height: 60px;

animation: spin 1s linear infinite;

border-top: 8px solid #3498db;

}

@keyframes spin {

0% {

transform: translate(-50%, -50%) rotate(0deg);

}

100% {

transform: translate(-50%, -50%) rotate(360deg);

}

}

#pageSpinnerOverlay .msg {

position: absolute;

padding: 8px;

font-size: 12px;

font-family: "Times New Roman";

font-style: italic;

text-align: center;

color: #000;

letter-spacing: 3px;

top: 58%;

left: 50%;

transform: translate(-50%, -55%);

}

STEP 3: Open JavaScript > "Function and Global Variable Declaration" code editor and paste the below code here-

function showSpinner(msg) {

let msgContainer = document.getElementsByClassName('msg');

msgContainer[0].innerHTML = msg;

$('#pageSpinnerOverlay').css('display', 'flex');

$('#pageSpinnerOverlay').css('flex-direction', 'column');

$('#pageSpinnerOverlay').show();

};

function hideSpinner() {

$('#pageSpinnerOverlay').hide();

};

STEP 4: Create Ajax Callback Process to perform your DML operation. Suppose, you want to increase employee salary by 10%. So, create a new Ajax Callback process and set it properties like-

Name: UPDATE_SALARY

PL/SQL Code: UPDATE employees

SET salary = salary + (Salary * .1);

STEP 5: Go to "Navigation" tab and select page. Open JavaScript > "Function and Global Variable Declaration" code editor. Paste the below code here-

function updateSalary() {

apex.message.clearErrors();

showSpinner('Data is uder processing. Please wait...');

apex.server.process(

'UPDATE_SALARY'

, {}

, {

dataType: 'text',

success: function() {

apex.message.showPageSucess('Update Salary successful');

}

}

);

hideSpinner();

}

Note: You can't noticed anything using this simple process because the process finished within a nano-second. If your process took a little bit longer time then you can notice its effect.

STEP 6: Select the button which is responsible to perform DML operation. Change it properties like-

Behaviour:

Action: Defined by Dynamic Action

STEP 7: Create a dynamic action under this button and set the action name as your choice. Select true action and set it properties like-

Action: Execute JavaScript Code

Code: updateSalary();

Save your page and run. Hope, you can cofigure your page successfully. This process will secure your page from intentionally click/execute another process when a process is already running. This process force the user to wait untill the executed process not finished. Let's enjoy.

Oracle APEX- Implement Drag & Drop Tree Structure

Oracle APEX tree region or page is not provided the drag and drop features by default. When you think to implement such kind of features, you need to think from out of the box. To add this features you need to build your tree structure from scratch. Using javascript library named "jstree", I have implemented "drag & Drag" features in my custom tree structure. Let me show the whole process step by step. You can find a visual assistance from here.

STEP 1: Create a blank page and named it "Drag & Drop Tree".

STEP 2: Select page and open JavaScript "File URLs" code editor. Copy the link from "cdnjs.com/libraries/jstree" and paste the below link here- https://cdnjs.cloudflare.com/ajax/libs/jstree/3.3.17/jstree.min.js (Step 2 or 3 any one is required).

STEP 3: (Optional) Copy the code from the above URL for offline support. Open nodepad and paste here. Save the file as jstree.js file. Go to APEX page and click on "Shared Component" icon. Click on "Static Application Files" and upload the jstree.js file. Copy the "Reference" link. Go back to APEX page and open JavaScript "File URLs" code editor. Paste the "Reference" link here.

STEP 4: Create a new region and set the below properties-

HTML Code : <div id='emp-tree'></div>

STEP 5: Create a button under the above region and set the properties like below-

Name: BTN_EXPAND_ALL

Label: Expand All

Region: Emp Tree

Slot: Edit

Button Template: Icon

Hot: Yes (Switch on)

Template Options:

Size: Small

Icon: fa-expand

STEP 6: Create another button and set the properties like below-

Name: BTN_COLLAPSE_ALL

Label: Collapse All

Region: Emp Tree

Slot: Edit

Button Template: Icon

Hot: Yes (Switch on)

Template Options:

Size: Small

Type: Danger

Spacing Left: Small

Icon: fa-compress

STEP 7: Click on the process icon from the navigation pane. Right click on "Ajax Callback" and Select "Create Process". Set it properties like-

Name: GET_DATA

PL/SQL Code: apex_json.open_array;

FOR i IN (

SELECT employee_id AS emp_id 

, NVL(manager_id,0) AS parent_id

, first_name||' '||last_name AS emp_name

FROM employees1

ORDER BY employee_id

) LOOP

apex_json.open_object;

apex_json.write('EMP_ID', i.emp_id);

apex_json.write('PARENT', i.parent_id);

apex_json.write('NAME', i.emp_name);

apex_json.close_object;

END LOOP;

apex_json.close_all;

STEP 8: Click on the "Navigator" icon and select page. Open the "Execute when Page Loads" code editor and paste the below code-

function buildTree(items) {

const map = {};

const root = [];

//Assign temporary unique key for tree

items.forEach((item, index) => {

map[index] = {...item, children:[], _uniqueId: index}

});

//Build Hierarchy

items.forEach((item, index) => {

if(item.PARENT === 0) {

root.push(map[index]);

} else {

//Find matching parent by EMPLOYEE ID

const parent = items.find(p=> p.EMP_ID === item.PARENT);

if(parent) {

const parentIndex = items.indexOf(parent);

map[parentIndex].children.push(map[index]);

}

}

});

return root;

}

//Convert to tree node recursively

function convert2tree(node) {

return {

text: node.NAME,

id: 'node_'+node.EMP_ID,

emp_id: node.emp_id,

parent_id: node.PARENT,

children: node.children.map(convert2tree)

}

}

apex.server.process(

'GET_DATA'

, {}

, {

dataType: 'json',

success: function(pData) {

try {

const treeData = buildTree(pData);

$('#emp-tree').jstree({

core: {

data: treeData.map(convert2tree),

check_callback: true

},

plugins:['dnd']

})

} catch (err) {

console.log('Tree build error = '+err);

}

},

error: function(err) {

console.log('AJAX error = '+ err);

}

}

)

$('#emp-tree').on('move_node.jstree', function(e, data) {

const nodeEl = $('#'+ data.node.id +'_anchor');

nodeEl.addClass('drag-highlight');

})

STEP 9: Select the "BTN_EXPAND_ALL" button and create a dynamic action here. Set it properties like-

Name: Expand Tree

Event: Click

Button: BTN_EXPAND_ALL

STEP 10: Select the newly create true action and set it propertieslike below-

Action: "Execute JavaScript Code"

Code: $('#emp-tree').jstree('open_all');

STEP 11: Select the "BTN_COLLAPSE_ALL_ALL" button and create a dynamic action here. Set it properties like-

Name: Collapse Tree

Event: Click

Button: BTN_COLLAPSE_ALL

STEP 12: Select the newly create true action and set it propertieslike below-

Action: "Execute JavaScript Code"

Code: $('#emp-tree').jstree('close_all');

STEP 13: Select page and open CSS > File URLs Code editor. Paste the link here- https://cdnjs.cloudflare.com/ajax/libs/jstree/3.3.17/themes/default/style.min.js (Step 13 or 14 any one is required).

STEP 14: Open another new tab and paste the link into the url- cdnjs.com/libraries/jstree. Copy the link (https://cdnjs.cloudflare.com/ajax/libs/jstree/3.3.17/themes/default/style.min.js) from here and paste it to the url. Copy the css code from here and open a blank nodepad and paste the css code here. Save it as style.css. Go to the APEX page and click on the "Shared Components" icon. Select "Static Application Files" and upload the style.css file. Copy the "Reference" link. Return to the page and open CSS > File URLs code editor. Paste the "Reference" link here.

That's it for today. Next episode, I'll show the rest of that series (Implement DML operation). 

Dynamic Content to PDF file generate in Oracle APEX

 In oracle APEX, it is not easy to generate pdf file from a dymic content specially from APEX 24.2 or earlier version. There is no built in process to generate pdf file from dynamic content. That's why some times we need to depend on thirds party tools to accomplish our jobs. 

I considered that you have already a dynamic content in your application's page. I try to resolve this critical business logic with the help of no third party tools, just only using 2 javascripts library. This process is discuss below step by step. For visual guidance, you follow the process from here (html2pdf).

STEP-1: Copy the jsPdf library file link from the Code Depository Network (CDN). Add the link to the page level javascripts file URLs.

STEP-2: Create javascript function to convert html document to PDF file. Click on the Function and Global Variable Declaration icon to open the code editor. Paste the below code here- 

function html2pdf(htmlContentId, callback) {

    let htmlContent = document.getElementById(htmlContentId);

    const { jsPDF } = window.jspdf;

    const doc = new jsPDF({

        orientation: 'p',

        unit: 'mm',

        format: 'a4'

    });

    doc.html(htmlContent, {

        x: 15,

        y: 15,

        width: 310,

        windowWidth: 1094,

        html2canvas: {

            scale: 0.16,

            useCORS: true,

            logging: false

        },

        callback: function(doc){

            const arrayBuffer = doc.output('arraybuffer');

            callback(arrayBuffer);

        }

    })

}

function pdfUsage(htmlContentId, state) {

    html2pdf(htmlContentId, function(arrayBuffer){

        const blob = new Blob([arrayBuffer], {type:'application/pdf'});

        const url = URL.createObjectURL(blob);

        const link = document.createElement('a');

        link.href = url;

        if (state === 'Download') {

            link.download = 'Application.pdf';

            document.body.appendChild(link);

            link.click();

        } else if (state === 'Preview') {

            link.download = 'Application.pdf';

            window.open(url, '_blank');

        } else if (state === 'Mail') {

            apex.message.clearErrors();

            const reader = new FileReader();

            reader.onloadend = function() {

                const base64data = reader.result;

                const clob = base64data.split(',')[1];

                

                apex.server.process(

                    'SENT_PDF_AS_ATTACHMENT'

                    , {

                        p_clob_01: clob

                    }

                    , {

                        dataType: 'text',

                        success: function() {

                            apex.message.showPageSuccess('Mail sent successfully with attachemnt');

                        }

                    }

                )

           }

           reader.readAsDataURL(blob);

        }

    })

}

STEP-3: Create an AJAX Callback process to send the mail. Click on the "Process" icon from the left side navigation pane. Right click on the "Ajax Process" and select "Create Process". Set it name as "SENT_PDF_AS_ATTACHMENT" and open the "PL/SQL Code" editor. Paste the below code here-

DECLARE

    l_file  BLOB;

    l_mail_id   NUMBER;

BEGIN

    l_file  := apex_web_service.clobbase642blob(

        p_clob  => apex_application.g_clob_01

    );

    l_mail_id   := APEX_MAIL.SEND(

                        p_to           => 'rummanalam@yahoo.com',

                        p_from         => 'rummancnsl@gmail.com',

                        p_body         => 'This is a test mail for html 2 pdf converted report attachment. Please ignore it.',

                        p_subj         => 'Html2pdf Report Converted & Sent as Attachment'

                );

    APEX_MAIL.ADD_ATTACHMENT(

            p_mail_id    => l_mail_id,

            p_attachment => l_file,

            p_filename   => 'Html2pdf',

            p_mime_type  => 'application/pdf');

END;

Now Save your page and run. Hope, it will perfectly sent an email perfectly. Keep it mind that, this tutorial is for only showing to convertion of Html to Pdf  nothing else. If you stuck on sending email then you need to configure your email first. You can find solutions to configure email from both Youtube and  here.

Let's Enjoy! Happy coding.

Build a Dynamic Hierarchical Tree Report with multiple columns in Oracle APEX

 We have to implement many requirements in our software development life cycle. This tree report is one of them requirements. The main requirements is need tree report with multiple columns to represent data properly and perfectly. By default in Oracle APEX, we can't develop multi-columns tree report. That's why, this is my efforts to overcome the limitation of default tree of APEX. So, lets start the discussion step by step. For visual assistance, you can follow the process from here.

STEP-1: Fisrt create a blank page. Then crate a region. Set it's below properties like

        Name:     Custom Tree Report

        type:        'Dynamic Content'

        PL/SQL Code: 

                                    DECLARE

    l_clob  CLOB;

BEGIN

    l_clob  := '<table class="a-IRR-table">

                    <tr>

                        <td class="a-IRR-header u-tC">Name</td>

                        <td class="a-IRR-header u-tC">Emp No.</td>

                        <td class="a-IRR-header u-tC">Manager ID</td>

                        <td class="a-IRR-header u-tC">Direct Subordinates</td>

                    </tr>

                    <tbody id="myTableBody">';

    

    FOR i IN (

        SELECT empno

            , ename

            , mgr AS manager_id

            , (SELECT COUNT(*)

                FROM emp 

                WHERE mgr = e.empno

            ) AS subordinates

            , SUBSTR(rec_level, INSTR(rec_level, 'L', -1)+1) AS rec_level

        FROM emp e 

        WHERE rec_level = 'LEVEL1'

    ) LOOP

        l_clob  := l_clob||

                    '<tr id="tr-'||i.empno||'">

                        <td class="u-tL"><span id="'||i.empno||'" style="padding-left:'||i.rec_level * 20||'px;" class="expand-collapse fa fa-plus-square-o" onclick="showDependent(this)"></span>'||i.ename||'</td>

                        <td class="u-tC">'||i.empno||'</td>

                        <td class="u-tC">'||i.manager_id||'</td>

                        <td class="u-tC">'||i.subordinates||'</td>

                    </tr>'  ;

    END LOOP;

    l_clob  := l_clob||'</tbody>

                </table>';

    RETURN l_clob;

END;

[Note: Use you own data table and you must need to add a column on your table/view named "rec_level" and data will be like 'level1', level2' etc.]

STEP-2: Create a AJAX Callback process to prepare child records of the parent. Click on the process tab icon from the left navigation pane. Right click on the 'Ajax Callbacks' and then select 'Create Process'.

Set the below properties as like-

Name:     ADD_DEPENDENT

Type:     Execute Code

PL/SQL Code:

        apex_json.open_array;

FOR i IN (

    SELECT empno

        , ename

        , mgr AS manager_id

        , (SELECT COUNT(*)

            FROM emp 

            WHERE mgr = e.empno

        ) AS subordinates

        , SUBSTR(rec_level, INSTR(rec_level, 'L', -1)+1) AS rec_level

    FROM emp e 

    WHERE mgr = apex_application.g_x01

) LOOP

    apex_json.open_object;

    apex_json.write('EMPNO', i.empno);

    apex_json.write('ENAME', i.ename);

    apex_json.write('SUBORDINATE', i.subordinates);

    apex_json.write('MGR', i.manager_id);

    apex_json.write('LEVEL', i.rec_level);

    apex_json.close_object;

END LOOP;

apex_json.close_all;

STEP-3: Go back to the navigator tab and select the page. Now go to the page properties and scroll down to "Function and Global Variable Declaration". Open the code editor and paste the below code here-

function showDependent(empId) {

    let eId = empId.id, i;

    if ($('#'+eId).hasClass('fa-plus-square-o')) {

        $('#'+eId).removeClass('fa-plus-square-o');

        $('#'+eId).addClass('fa-minus-square-o');

        

        apex.server.process(

            'ADD_DEPENDENT'

            , {

                x01: eId

            }

            , {

                dataType: 'json',

                success: function(pData) {

                    let targetRow = document.getElementById('tr-'+eId);

                    for (i = 0; i < pData.length; i++) {

                        let tr = document.createElement('tr');

                        tr.setAttribute('id', 'tr-'+pData[i].EMPNO);

                        tr.setAttribute('data-parent', eId);

                        // create reprot column one by one

                        let eName = document.createElement('td');

                        eName.classList.add('u-tL');

                        if (parseInt(pData[i].SUBORDINATE) > 0) {

                            eName.innerHTML = '<span id="'+pData[i].EMPNO+'" style="padding-left:'+(pData[i].LEVEL*20)+'px;" class="expand-collapse fa fa-plus-square-o" onclick="showDependent(this)"></span>'+pData[i].ENAME;  

                        } else {

                            eName.innerHTML = '<span id="'+pData[i].EMPNO+'" style="padding-left:'+(pData[i].LEVEL*20)+'px;" class="expand-collapse fa fa-plus-square-0" onclick=""></span>'+pData[i].ENAME;   

                        }

                        //FOR empno column

                        let empNo = document.createElement('td');

                        empNo.classList.add('u-tC');

                        empNo.innerHTML = pData[i].EMPNO;

                        //For manager Id column

                        let mgr = document.createElement('td');

                        mgr.classList.add('u-tC');

                        mgr.innerHTML = pData[i].MGR;

                        //FOR DIRECT SUBRODINATES column

                        let dirSub = document.createElement('td');

                        dirSub.classList.add('u-tC');

                        dirSub.innerHTML = (parseInt(pData[i].SUBORDINATE) > 0)?pData[i].SUBORDINATE:'';

                    

                        //APPEND these column to the TR

                        tr.append(eName, empNo, mgr, dirSub);

                        //Add the row after the appropriate parent record

                        targetRow.after(tr);

                    }

                }

            }

        )

    } else {

        hideDependent(eId); //later will be declare

        $('#'+eId).removeClass('fa-minus-square-o');

        $('#'+eId).addClass('fa-plus-square-o');

    }

}

function hideDependent(eId) {

    let childRows = document.querySelectorAll("[data-parent='"+eId+"']");

    childRows.forEach(function(row) {

        let childId = row.id.replace('tr-',""); // Get the numeric emp number.

        hideDependent(childId); // recursively remove the child row.

        row.remove(); // Finally remove the childs

    });

}

That's it. Now save and run. Hope, you have done a great job. let's enjoy the fun