Oracle APEX Tips & Techniques

In Oracle APEX, validating Interactive Grid (IG) data at row level, is easy and straight forward. You just need to select "Editable Region" for the validation and then you can use IG column values using :COLUMN_NAME syntax in SQL and PL/SQL code. However, if you want to create a validation at table level or validation which uses multiple rows data, then it becomes tricky. Let's consider a simple example of budget planning, where you can allocate percentage for each category, like 10% for healthcare, 10% for education etc. Here, we need to implement a simple table level validation to ensure total allocation % does not exceed 100. Please refer  Interactive Grid - Client Side Aggregate Validations  for implementing this validation entirely on client side. You can use technique explained below for more complex validations that can't be done on client side (checking data from multiple tables, number of rows in IG are more than 50) Before jumping into the implemen...

Friendly URLs! It's been one of the most wanted feature in APEX from very initial days. It's one of the main complaints we hear from customers, especially for websites which are open for public. There are several benefits of having a friendly URL besides better user experience. Finally, the wait is over! APEX team has implemented this feature, probably in the most simplest way possible (for APEX developers). Big Kudos to the APEX Team. All you need to do is, just turn on a flag in application properties. Yes, you read it right.. Now, after enabling this flag, all APEX URLs will switch to new friendly URLs New URL Format: Example link - https://apex.oracle.com/pls/apex/hari/r/demo_app/home?session=714815362925554 Part-1:  https://apex.oracle.com/pls/apex/ - Host details, there is no change here Part-2: /hari/r -  Here hari  is work-space path prefix and  r  stands for router. You can modify "Path Prefix" at "Workspace Administration...

One complaint I often hear about APEX applications is, issues users may face when they open application in multiple browser tabs/windows. Some pages which contains only reports or dash board pages may work fine even when opened in multiple tabs/windows. However, some pages may have issues like wrongly updating session states, seeing mixed data from two separate records or worst case wrong data being updated to DB. General advise I give to users is, please use different browsers, if you want to open same application/page in two tabs . But, can we somehow detect that user has opened more than one tab and show the message directly from the application itself? And the answer is, Yes, we can and here is the link to demo . If you want to implement this technique in your APEX application or page, all you need to do is Download noMulTabs.js  and add this JS file to your APEX application. If you are not sure what is the best way to do it, you can refer  Adding JavaScript to a...

With APEX Version 5.0, new Application Context APEX$SESSION is introduced. User Name, Session ID and Work-space ID are populated into this context. I often read, using  APEX$SESSION Context is faster (gives better performance) than using V function, however, how fast is it? I have never measured it. It's been on my TODO list for long time and finally here is short test I did to measure the performance using both the approaches. DB Setup: I have created two views in APEX Parsing schema as follows. View demo_sys_context_v uses APEX$SESSION Context to get current APEX user name. CREATE OR REPLACE VIEW demo_sys_context_v  AS     SELECT         object_name     FROM         user_objects     WHERE         object_name = sys_context(             'APEX$SESSION'             ,'APP_USER'     ...

Oracle APEX offers several beautiful chart types which are based on Oracle JET Data Visualizations. Gantt Chart is one such beautiful and useful chart. However, when I have searched in Google for some help on Gantt Charts, there are not many blogs posts talking about it. So, I thought, I could write one with few basic tips which I have learned this year. I have used Gantt Chart to display employees calendar data and my targeted output was something like below. Pic-1 Looks simple, correct? However, it's little tricky to get there. Multiple Tasks Per Row: When I look at the output, first I thought, I will have to write a query which gives tasks data and employee data with 1 row per task. That is, if there are 10 tasks to be displayed, then there should be 10 rows in SQL query output. But, it's not correct. Instead, I should have 1 row for each task + 1 row for each employee (parent). So I need to write a query which will output data in below format. Pic-2 A...

Do you know any real use case for condition type "Never"? Do you know what is the difference between NOT SELECTING ANY  read only condition and selecting option "Always"? If your answer is "No" to either of these questions, then you may find this blog post useful. Read Only Condition: You can specify read-only condition at Page Level, Region Level or at Item Level. Read Only condition specified at page level applies to entire page. When the condition is met, all the items rendered on the page (including items from global page) will be displayed as read-only. Similarly, Read Only condition specified at region level applies to all items defined under that page. Advantages of using "Read Only" condition: Rendering : APEX automatically takes care how to display items in read only mode and what data to display. For e.g. for LOV based items, APEX will automatically display "Display" value. Security : When Item is read only, users s...

There was a requirement to build a page where users can  search for data in a report and then choose a row to be added to Interactive Grid (IG),  besides regular way of adding rows to IG. There are two approaches (could be more) to achieve this. Old school approach is adding a row to APEX Collections when user chooses a row in report and using same collection in IG query. Other approach could be, doing something with JavaScript (JS) and build solution on top of IG framework using IG JS APIs, without using collections. When we convert this requirement into technical specification, essentially it has two parts. Adding new row to IG via JS Set values for newly added row via JS Luckily, there are JS APIs and functions already available to achieve both the points. Adding new row to IG via JS Adding new empty row via JS is rather straight forward and easy. // "order" is IG region static ID var $widget = apex.region( 'order' ).widget(); var $gr...

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ...