Posts

Oracle Quick Tips #2

-
Image
In this blog I will be explaining how to convert Numbers to Roman Numerals using a SQL query. Existing Report:   I have an Interactive Report where I  have four columns (ID, Name, Salary and Rank). I am displaying the rank of the employees that is ordered by the salary in descending order.  Requirement: My requirement is to display the rank in roman numbers.  How can this be achieved using a simple SQL query? Follow the below steps to achieve this. Let me explain this with an example: 1) Create a table create table t_employee ( emp_id number, emp_name varchar2(100), salary number ); 2) Add the required data into the table. 3) Execute the below SQL query  select emp_id, emp_name, to_char(salary_rank, 'RN') as roman_sequence from ( select emp_id, emp_name, dense_rank() over(order by salary desc) salary_rank from t_employee ); Note: In the above c
Post a Comment
Read more

Button with Spinner/Progress Indicator

-
Image
In this blog I will be explaining, how to show a Button with Spinner/Progress Indicator in Oracle APEX. In Oracle APEX, by default the spinner will be displayed on the page but in one of my requirements I had to display the spinner in a button instead of displaying it in the page. So I followed the below steps to achieve it. 1) Create a Region in the page, 2) Create a Button in the region. (In this example:  P37_PROGRESS_BUTTON ) 3) Add the class  custom-button-spinner in the Appearance -> CSS Classes section of the button.  4) Add the below code in the Execute When Page Loads section of the page. $(document).on("click",".t-Button.custom-button-spinner",function(){ $element = $(this); $element.addClass("apex_disabled"); $element.find(".t-Button-label").append('<span aria-hidden="true" class="fa-spinner fa fa-anim-spin margin-left-sm js-custom-spinner"></span>'); }); Note:   The above
2 comments
Read more

Get the file details(Single and Multiple) that are selected using a File Browse Item using JavaScript in Oracle APEX

-
Image
  In this blog I will be explaining, how to get the details of a file(s), selected using a File Browse Item, using JavaScript in Oracle APEX. For Single File: 1) Create a Region . 2) Create an Item (Type: File Browse ) in the Region. (In this example:  P1_FILE_BROWSE ). 3) Create 3 More items in the page to store the File Name, File Size and File Type.                  (In this example: P1_FILE_NAME , P1_FILE_SIZE , P1_FILE_TYPE )  4) Create an On Change Dynamic Action on the  P1_FILE_BROWSE item. 5) Create a True Action (Type: Execute JavaScript Code ) and paste the below code. var $filebrowseItem = $("#P1_FILE_BROWSE"); var filebrowseItemLength = $filebrowseItem[0].files.length; var fileArray = $filebrowseItem[0].files; if (filebrowseItemLength > 0) { for (var i = 0; i < filebrowseItemLength; i++) { var fileName = fileArray[i].name; var fileSize = fileArray[i].size; var fileType = fileArray[i].type; apex.item( &q
Post a Comment
Read more

Allow Only Number/Decimal values In a Number/Text Field Using JavaScript in Oracle APEX

-
Image
In this blog I will be explaining how to restrict users from typing only numbers/decimal values in a Number/Text Item in Oracle APEX. 1) Create a Region .    2) Create an Item in the page (Type: Text Field or Number Field ). 3) To allow only Numbers, paste the below code in the Page ->  Execute When Page Loads Section . $("#P30_NUMBER_FIELD_ITEM").keypress(function(event){ event = (event) ? event : window.event; var charCode = (event.which) ? event.which : event.keyCode; if (charCode > 31 && (charCode < 48 || charCode > 57)) { return false; } return true; });   Note: Replace P30_NUMBER_FIELD_ITEM with your Item name. 4) To allow only Decimal values, paste the below code in the  Page  ->  Execute When Page Loads Section . $("#P30_DECIMAL_FIELD_ITEM").keypress(function(event){ event = (event) ? event : window.event; if ( event.which != 0 && (event.which < 48 || event.which > 57) &am
3 comments
Read more

Highlight Interactive Grid cells using MutationObserver API in Oracle APEX

-
Image
This blog explains how to highlight the Interactive Grid cells using JavaScript instead of using the inbuilt highlight feature. Let us start with a one-line information about Mutation Observer:  The Mutation Observer provides the ability to watch for changes made in the DOM tree. What advantage does the Mutation Observer have when compared to the normal way of highlighting. Let me tell this with an example.    Customer: I want an Editable  Interactive Grid that highlights the status field on Page Load and when the changes are made and saved. The color must also remain when sorting, filtering etc.. is applied to the IG.    In the usual way, if I have to achieve this, I should create a JavaScript function that highlights the IG and call the function "on page load, on save of the IG and on page change of the IG" . So, I was thinking, how can this be achieved using a single piece of code instead of calling it in each event. Mutation Observer helped me to achieve this use case. F
Post a Comment
Read more

Show Help Text using JavaScript in Oracle APEX

-
Image
  This blog explains, how to Show Help Text using JavaScript in Oracle APEX.  Follow the below steps to achieve this. 1) Create a Region .  2) Create a Button in the region. 3) Create an  On Click dynamic action for the button. 4) Create a True Action (Execute JavaScript Code) and paste the below code. apex . theme . popupFieldHelp ({     title :   "Help" ,     helpText :   'This is a help text box' }); The Help Text also accepts HTML. Below is an example on how to use HTML in the help text. apex . theme . popupFieldHelp ({     title :   "Help Text with HTML" ,     helpText :   '<style>' +                    '.li-color {color:blue;}' +                '</style>' +                '<ul>' +                    '<li class="li-color"><b>This is a help text box that accepts HTML formatting</b></li>' +                '</ul>' }); Click here for DEMO Hap
2 comments
Read more

IG Hack - #4, Change the column width of Interactive Grid using JavaScript in Oracle APEX

-
This blog explains, how to change the column width of and Interactive Grid using JavaScript. Follow the below steps to achieve this: Example - 1: Set the Column Width for a single Column. 1) Put the below JavaScript Code in the Page -> JavaScript -> Execute When Page Loads section. apex . region ( "EMP_IG" ). call ( "getViews" ). grid . view$ . grid ( "setColumnWidth" , "EMPLOYEE_NAME" ,2 00 );   Note:  In the above code, the width of the Employee Name column is set to 200.  Replace EMPLOYEE_NAME with the name of your column that you want to set the width for. Replace EMP_IG with the Static ID of your Interactive Grid. Example - 2:  Set the Column Width for all Columns. 1)   Put the below JavaScript Code in the  Page -> JavaScript -> Execute When Page Loads  section. var  igView  =  apex . region ( "EMP_IG" ). call ( "getViews" ). grid . view$ ,     colArray   =   [];      colArray  =  igView . grid ( &q
1 comment
Read more

IG Hack - #3, Different ways to Hide Save Button Using JavaScript/CSS in Oracle APEX Interactive Grid

-
In this post we will see, Different ways to hide save button using JavaScript/CSS in Oracle APEX Interactive Grid. Method 1:   Using any one of  the below  JavaScript code  in the Execute When Page Loads section of the page. apex.region( "YOUR_IG_STATIC_ID" ). call ( "getActions" ). hide ( "save" ); (OR) $("# YOUR_IG_STATIC_ID .a-Button.a-Toolbar-item.js-actionButton.a-Button--hot[data-action='save']").hide(); Method 2:   Using the below  JavaScript code in  the JavaScript Initialization Code  section in the Interactive Grid. function (config) {     var $ = apex.jQuery,         toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),         toolbarGroup = toolbarData.toolbarFind( "actions2" );     //Hide save button     toolbarGroup.controls.splice(toolbarGroup.controls.indexOf("save"),1);     config.toolbarData = toolbarData;   return config; } Method 3:   Using the below CSS in the  CSS >  Inline   
Post a Comment
Read more

Disable update for a particular column in Oracle APEX Interactive Grid

-
Image
This blog explains how to disable update for a particular column in Interactive Grid using the  Read Only property of a column .  Should we use JavaScript to achieve this? The answer is, NO!!!  Follow the below steps to achieve this 1) Create an Interactive Grid 2) Select the column that has to be made read only when trying to update.  In this example, the  ROLE Column has is made read only when trying to update 3) Scroll down to the Read Only property of the column and do the changes as shown in the below screenshot. Note: It is not mandatory to use 1=1. Any expression can be written. The column will be disabled if the given expression is satisfied. Its is also not mandatory to use Type -> Expression. Other Types can also be used based in the requirement. 4) Save and Run the page. Click here for DEMO Happy CODING!!! Thank you :)
Post a Comment
Read more

Display Inline Popup as Callout in Oracle APEX

-
Image
In this blog I will be explaining, how to display an Inline Popup as a callout. Follow the below steps to achieve this: 1) Create a Region   2) Create a Button in the region, provide a static ID to the button ( Advanced -> Static ID)         3) Create another Region   and select the below properties for the region. Layout -> Position -> Inline Dialogs (Optional) Appearance -> Template -> Inline Popup 4) Click on the Template Options (Appearance -> Template Options) of the region. The below mentioned points are mandatory:  Check the Display Popup Callout checkbox.  Choose a Callout Position f rom the list . All the other options can be chosen based on the requirement. 5) Paste the below piece of code in the Advanced -> Custom Attributes section of the region.         data-parent-element="#YOUR_STATIC_ID" In the above piece of code, replace YOUR_STATIC_ID with the static ID that has been provided for the button.    The Popup Callout is NOT restricte
Post a Comment
Read more

Legends using Page Item in Oracle APEX

-
Image
This blogs explains, how to show page items as legends in Oracle APEX.  Follow the below steps to create a legend 1) Create an Item. 2) Change the Item Type to Display Only. 3) Provide a value to the item ( Source -> Type -> Select any type as per the requirement).  4) Paste the below code in the Label  section. <svg class="legend-svg"><rect class="legend-rect legend-1"></rect></svg> 5) Paste the below code in the CSS - > Inline section of the page. .legend-svg { height: 30px; width: 40px; } .legend-rect { stroke-width: 3; stroke: rgb(0,0,0); height: 20px; width: 40px; } .legend-1 { fill:#E38211; /*color of the legend*/ } Note: The above CSS ,  Layout and Appearance  of the item can be modified as per the requirement.  6) Save and Run the page Please see the How to..   section   in the   demo for better understanding. Reference: https://www.w3schools.com/graphics/svg_rect.asp Click here for
Post a Comment
Read more

IG Hack - #2 Display Column Name from Query as Interactive Grid Column Heading

-
Image
This blog explains, how to display column name from query as IG column heading. Let me explain this with a use case: Developer 1: The region source of my IG is Function Body returning SQL query. I want to display the column alias name given in my SQL queries as the IG column heading. Can this be achieved? Developer 2: Ofcourse, yes!  You can also display the column name/alias even if the region source is SQL Query. Let's Start.... 1) Create an IG in the page. Select the region source as SQL query. Paste the below code. SELECT 'Akil' as "Employee_Name", 'Oracle' as "Employee Department", 'India' as "Country" FROM dual UNION SELECT 'John' as "Employee_Name", 'JAVA' as "Employee Department", 'Canada' as "Country" FROM dual UNION SELECT 'Max' as "Employee_Name", 'IOT' as "Employee Departme
1 comment
Read more

Change the Column Width of a POPUP LOV Item In Oracle APEX

-
Image
Why should I change the Column Width of a Popup LOV when I have an option to stretch it to view the whole content? Let me explain this with a use case: Customer: I want to display a three columns in my Popup LOV and the first column must be fully visible. Akil: There is an option to stretch the column to view its whole content. Customer: No, I want it to be displayed fully. I don't want to stretch the column manually. Akil: Ok, I can make this possible! Let's Start.... 1) Create an Item in the page (In this example: P16_EMP_NAME). 2) Create the list of values that will display the multiple columns in the Popup LOV. I created four columns in my Popup LOV. Refer below screenshot. To know how to display multiple columns in a Popup LOV, see  Karkuvel Raja's Post   3) Paste the below JavaScript code in the J avaScript Initialization Code section of the Popup LOV item (P16_EMP_NAME). function(options) { var col, columns = options.columns; col = columns.NAME; /
3 comments
Read more

Oracle Quick Tips #1

-
Image
LIKE &  REGEXP_LIKE LIKE:- The Oracle LIKE condition allows wildcards to be used in the WHERE clause of a  SELECT,INSERT,UPDATE or DELETE statement. REGEXP_LIKE:- The Oracle REGEXP_LIKE condition allows you to perform regular expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. From the above statements we can conclude that there is no much difference in the functionality of a LIKE and REGEXP_LIKE conditions. But when we use these operators to find/match multiple values/words the repetition can be avoided by using REGEXP_LIKE. LIKE Example: WHERE (COLUMN LIKE '%value1%' OR COLUMN LIKE '%value2% OR....) REGEXP_LIKE Example: Much Easier The pipe symbol '|' inside the regular expression acts as the OR operator and the parenthesis () form the subexpression that is needed for that OR. WHERE REGEXP_LIKE(COLUMN,'(value1|value2|value3...)') OR &  ANY OR:- The Oracle OR  operator is a logical operator that comb
Post a Comment
Read more

Close Modal Dialog When Clicked Outside in Oracle APEX

-
Image
Modal Dialog  page in Oracle APEX by default can be closed my clicking the (x) icon/button on the top right corner of the modal page. In this post I will be explaining, how to close the modal dialog when clicked outside. Please follow the below steps to achieve this: 1) D efine the following JavaScript function in the Function and Global Variable Declaration  section on the parent page (The page from which the modal dialog page is being called/opened). function closeModalDialogPage(element){ $('.ui-widget-overlay').click(function(){ $(element).dialog('close'); }); } Note: If you’re going to be using this in more than one place, its better to define it either on the page 0 or in a global JavaScript file. 2) Create an  open callback that will trigger the close dialog method when clicking outside the dialog.  We have to set call the function when the dialog opens. Define the below code in the  Attributes  section of the Modal Dialog page. op
Post a Comment
Read more

Display and Edit CLOB Content in Oracle APEX

-
Image
In this post I will be explaining how to display  and edit a CLOB content in Oracle APEX.  Please follow the below steps: 1) Create a table T_EMPLOYEE CREATE TABLE T_EMPLOYEE ( EMP_ID NUMBER, EMP_NAME VARCHAR2(100), DESCRIPTION CLOB ); 2) Insert the required data into the T_EMPLOYEE table.                           3) Create a page in the application.  (In this example Page - 5) 4) Create another page with the Page Mode as Modal Dialog (In this example Page - 6)   5) Create a region and two items in Page - 6. The Items are:     P6_EMP_ID -> Type -> Hidden  (Value Protected: No)     P6_EMP_DESCRIPTION ->   Type  ->  Text Area   (or) Rich Text Editor 6) Now navigate back to Page - 5 and create a Region ( Type -> Interactive Report ) 7) Paste the below code in the Source -> SQL Query of the Interactive Report SELECT EMP_ID, EMP_NAME, 'Preview' as "PREVIEW" FROM T_EMPLOYEE; 8) Go to the PREVIW column
1 comment
Read more