In this edition, we will dive further into the dynamic world of Oracle APEX, bringing you expert tips and actionable tricks to elevate your application development. We have curated this blog around some JavaScript hacks. So, let’s start.
Useful JavaScript hacks with Oracle APEX
As the entire APEX community is enthralled by the features in 24.1, we thought why not go back to the basics and showcase how JavaScript makes a difference to our applications and club it with PL/SQL making it a lethal combination, especially while executing complex Validations.
In this APEX blog post, we will cover various examples implemented across APEX applications, with actual JavaScript code snippets that can be plugged in and used as in your APEX pages. We also show a couple of examples where a combination of PL/SQL and JavaScript help build rock-solid validations. Additionally, we also cover a server-side PL/SQL validation on file upload. We are going to cover the below hacks in this blog.
- 1. Disabling Items like select lists, text field, text area in JavaScript
- 2. Make a 3D Pie-Chart using simple JavaScript
- 3. Enable Print page on a button-click in JavaScript
- 4. JavaScript validation showing Errors in notifications
- 5. Complex validations with a combination of PL/SQL and JavaScript
- 6. Copy Text onclick of an icon in JavaScript in an Interactive Grid
- 7. PL/SQL validation during file upload for Security, to validate and disallow double extensions like filename.exe.pdf, thereby blocking malicious code to be uploaded during file upload making your applications further secure.
- 1. Disabling select list, text fields & text area in JavaScript
Javascript
On Page Load -> Execute JavaScript Code
if ($v(‘P3_DISABLE_ITEMS’) == ‘YES’) //OR ( $v(‘P3_MODE’) !== ‘EDIT_SR’ ){//Make Select-List Read Only
$(‘#P3_IS_BILLABLE’).attr(“style”, “pointer-events: none;background-color:#F3F6F7”);
//Make input/text box Read Only
$(“#P3_QUANTITY”).css(“background-color”, “#F3F6F7”);
$x(‘P3_QUANTITY’).readOnly = true;
$(‘#P3_EXPECTED_DELIVERY_DT’).css(“background-color”, “#F3F6F7”);
$x(‘P3_EXPECTED_DELIVERY_DT’).readOnly = true;
//Make text-area box Read Only
$(“#P3_SPECIFICATIONS”).css(“background-color”, “#F3F6F7”);
$x(‘P3_SPECIFICATIONS’).readOnly = true;
}
- 2. Make a 3D Pie-Chart using simple JavaScript
Region: Type: Chart
Attributes: Type: Pie, Advanced -> Initialization JavaScript Function
// for 3D effectfunction( options ) {
options.styleDefaults = $.extend ( options.styleDefaults, {threeDEffect : “on”});
return options;
}
BEFORE: Pie chart (standard)
AFTER: Pie chart – (3D with JavaScript)
- 3. Enable Print on a button click using simple JavaScript
Create a Dynamic Action: on-click of a Button Print.
Event: Click Selection Type: Button, Button: PRINT_PAYSLIP
Create True Action.
Action: Execute JavaScript Code.
Paste the following JavaScript code snippet.
- 4. JavaScript validation (not null) showing Error alert in notifications
apex.message.clearErrors();if ($v(“P18_TIMECARD_ID”).trim() == ”) {
apex.message.showErrors({
type: “error”,
location: [ “page”],
message: ‘Not Timecard selected, please select a Timecard to submit’,
unsafe: false
});
return false;
/* This is important, it stops the next action(s) from running. */
}
- 5. Complex validations with JavaScript and PL/SQL in cohesion
The validation requirement is complex in this case where before Submitting Time, the user must query the database via PL/SQL Procedure for some complex validation scenarios.
On-click of a Button, SUBMIT_TIME Create the following processes in Sequence.
Process 1: Execute Server-side Code – PL/SQL – Call a procedure that returns the error as an output parameter and Set the Page Item with the output parameter.
PL/SQL Process: called on-click of Submit TimeDECLARE
p_out_exception varchar2(500) := ”;
p_out_hrs_to_enter number := 0;
BEGIN
PROC_TIMECARD_SUBMIT(:P18_TIMECARD_ID, p_out_hrs_to_enter, p_out_exception);
// set the following page Items with output parameter returned from the Procedure
apex_util.set_session_state(‘P18_ERROR_SUBMIT_TIME_CARD’, p_out_exception);
apex_util.set_session_state(‘P18_HOURS_TO_ENTER’, p_out_hrs_to_enter);
EXCEPTION when others then
apex_util.set_session_state(‘P18_ERROR_SUBMIT_TIME_CARD’, ‘UNKNOWN_ERROR’); //set the error Page item
END;
Process 2: Execute JavaScript Code: Alert relevant messages on the basis of the following page ITEM: P18_ERROR_SUBMIT_TIMECARD.
if ($v(“P18_ERROR_SUBMIT_TIME_CARD”).trim() == ‘TIME_CARD_NOT_OK_FUTURE_PROJECT_ENTRY’) {apex.message.showErrors({
type:“error”,
location:[ “page” ],
message: ‘<b>Project Time Entry for a future Week Ending is not permissible’,
unsafe: false
});
return false;
}
if ($v(“P18_ERROR_SUBMIT_TIME_CARD”).trim() == ‘TIME_CARD_LESS_THAN_40_HOURS’) {apex.message.showErrors({
type: “error”,
location: [ “page” ],
message:‘Project Time Entry needs to be for a minimum of 40 hours per week’,
unsafe: false
});
return false;
}
if ($v(“P18_ERROR_SUBMIT_TIME_CARD”).trim() == ‘UNKNOWN_ERROR’) {apex.message.showErrors({
type:“error”,
location:[ “page” ],
message:‘Unknown error occurred while Submitting Timecard. Please contact system administrator’,
unsafe:false
});
return false;
}
Process 3: Execute JavaScript Code to Reset the Page Items
$s(“P18_TIMECARD_ID”,”);$s(“P18_TIMECARD_STATUS”,”);
Process 4: Execute JavaScript – to Refresh the Region (in context)
Action: RefreshSelection Type: Region
Region: <select the Region in context >;
- 6. Copy text on-click of an icon in an Interactive Grid in JavaScript
Step 1: Paste the following code in the JavaScript section after selecting the Page in the rendering panel.
function toClipboard(incomingText) {// apex.message.alert (‘incoming text: ‘+incomingText);
let text = incomingText;
let result = text.includes(“=”);
if (result) // if the string text has an = sign (for ex: Demokey=22984)
{
const myArray = text.split(“=”);
//apex.message.alert (‘The Value to be copied is: ‘+myArray[1]);
text = myArray[1];
// Copies the 2nd part of the text, i.e. after the =
}
else
// Copy text as-is
{
//apex.message.alert (‘The Value to be copied is: ‘+text);
}
navigator.clipboard.writeText(text).then(() => {
/* clipboard successfully set */
//apex.message.alert (‘Copied text: ‘+incomingText);
//$(“#alert_rgn1″).html(‘<i style=”color:black;font-size:12px;”></i> Copied to clipboard.’);
//$(“#alert_rgn1”).dialog({modal:true,resizable: false,minHeight:2,width:10,dialogClass:’dm-IconDialog-infoTitle col col-5′});
console.log(‘Copied text’);
}, () => {
/* clipboard write failed */
console.log(‘Copy text failed’) ;
});
}
Interactive Grid – UI
Step 2: Create an Interactive Grid with a query that shows data and the Copy icon image as one field, onclick of the icon – JavaScript is called to copy text.
Interactive Grid Query that shows text data and the Copy icon image in one field and the text data as other columns with a CSS class applied. This column can then be Hidden from the Interactive Grid UI.
selecturl,
nvl2(url,'<a href=”‘||url||'” target=”_blank” title=”Click to Launch”>’||application_name||'</a>’, application_name) app,
‘<font color=”black”>’||user_name||'</font>’||decode(user_name,null,”,’ <span aria-hidden=”true” class=”fa fa-copy”></span>’) user_name_conditional_text,
user_name,
‘<font color=”black”>’||password||'</font>’||decode(password,null,”,’ <span aria-hidden=”true” class=”fa fa-copy”></span>’) password_conditional_text,
password,
from application_users
Make the User_name_conditional_text column as a Rich Text Editor.
Make the USER_NAME as a Link
Call JavaScript on the ITEM, Make the Target Type: URL, on the icon, to copy the previous text in the previous Item.
- 7. PL/SQL validation during file upload for Security, to validate and disallow double extensions, for example filename.exe.pdf
The need was to create a validation on a File Upload Item, to allow only pdf, doc, xlsx, pptx, txt extensions and to ensure double extensions like filename.exe.pdf, are not allowed thereby blocking any malicious code that will be uploaded via your Forms Item Upload, making applications further secure.
Create 2 validations, (the 1st Reg-ex validation, which is optional) and a PL/SQL validation to block double extensions.
Validation 1: Regular expression (to check file extension and allow files of a particular type.
Validation: check valid file ext for attachment.
Type: Item matches Regular Expressin
Item: P3_SR_ATTACH
Regular Expression: ([0-9a-zA-Z :\\-_!@$%^&*()])+(.jpg|.JPG|.png|.PNG|.jpeg|.JPEG|.gif|.GIF|.xls|.xlsx|.XLSX|.pdf|.PDF|.doc|.DOC|.docx|.DOCX|.ppt|.PPT|.pptx|.PPTX|.zip|.ZIP|.txt|.TXT|.eml|.EML)$
Validation 2: validate malicious extensions (for double extensions).
Type: Function Body (returning Boolean)
Language: PL/SQL
declarel_count number := 0;
type array_t is varray(35) of varchar2(20);
array array_t := array_t(‘.exe’,’.EXE’,’.PHP’,’.php’,’.rar’,’.RAR’,’.jar’,’.JAR’,’.msi’,’.MSI’,’.msp’,’.MSP’,’.msc’,’.MSC’,’.application’,’.APPLICATION’,’.pif’,’.PIF’,’.com’,’.COM’, ‘.scr’,’.SCR’, ‘.gadget’,’.GADGET’,’.hta’,’.HTA’,’.cpl’,’.CPL’,’.scr’,’.SCR’);
BEGIN
— for r in IN (‘.exe’,’.EXE’,’.PHP’,’.php’,’.rar’,’.RAR’,’.jar’,’.JAR’,’.msi’,’.MSI’,’.msp’,’.MSP’,’.msc’,’.MSC’,’.application’,’.APPLICATION’,’.pif’,’.PIF’,’.com’,’.COM’, ‘.scr’,’.SCR’, ‘.gadget’,’.GADGET’,’.hta’,’.HTA’,’.cpl’,’.CPL’,’.scr’,’.SCR’)
for i in 1..array.count
LOOP
select regexp_count(:P3_SR_ATTACH, array(i) ) into l_count from dual;
if l_count > 0 then
return FALSE;
end if;
END LOOP;
return true;
EXCEPTION when others then
APEX_ERROR.ADD_ERROR (
p_message => ‘Error validating file type: ‘||sqlerrm,
p_display_location => apex_error.c_inline_in_notification );
END;
If you have any questions or would like to know more about Oracle APEX development and how Conneqtion can assist you, get in touch with us at [email protected].
