Roels Blog

 If you run your APEX application - like a Form based on the EMP table - and test if you can change the value of Department to something else then the standard values of 10, 20, 30 or 40, you'll get a nice error message like this: But it isn't really nice, is it? So what do a lot of developers do? They create a validation (just) in order to show a nicer, better worded, error message like "This is not a valid department".  And what you then just did is writing code twice : Once in the database as a (foreign key) check constraint and once as a sql statement in your validation. And we all know : writing code twice is usually not a good idea - and executing the same query twice is not enhancing your performance! So how can we transform that ugly error message into something nice? By combining two APEX features: the Error Handling Function and the Text Messages! Start with copying the example of an Error Handling Function from the APEX documentation. Create this function ...

There are some, maybe rare, use case when you want to set the " Suppress Change Event " switch of a Dynamic Action to " Yes ". Usually that's only necessary when the Dynamic Action is fired On Change  and executes a Set Value  with the same field as a target. There might be a chain of events leading to this or just one. If you forget to set it you'll end up in an endless loop and your browser will choke. That works all fine and as expected with regular Page Items. However ... when you apply this to a column in an Interactive Grid something unexpected happens. When you set the Suppress Change Event of a Dynamic Action that fires on change of a column to Yes , the value of that item seems empty when you leave that field (column)! If you navigate back into that field, the (new) value does show up... You can call it a feature, you can call it a bug, but bottom line it is unexpected and undesired behaviour. The solution is to call the Grid method SetActiv...

After a few nights of good sleep (that means more than the average 4 to 5 hours at Kscope), it is time to sit back and evaluate what happened last week. I arrived on Thursday night before the event, just in time to catch Danny Bryant at the bar for "just one last beer" (I don't know how often I heard, and used, that phrase last week). The whole Friday was filled with an all day Board Meeting, mainly discussing the future of ODTUG, a reception and a fine dinner. Since a couple of years, Saturday is the Community Service Day. This year, an impressive number of people showed up and were transferred to "Up Orlando", to make its easier for them to help the people that really need some help. All kinds of different activities were done, like cleaning, filling shelves, painting chairs etc.  After that there is another tradition: bag stuffing. All those (about 1500) bags didn't get delivered with all those goodies in there! A number of volunteers created the most ...

If you created your own "updatable reports" or your custom version of tabular forms in Oracle Application Express, you'll end up with a query that looks similar to this one: then you disable the " Escape special characters " property and the result is an updatable multirecord form. That was easy, right? But now we need to process the changes in the Ename column when the form is submitted, but only if the checkbox is checked. All the columns are submitted as separated arrays, named apex_application.g_f0x - where the "x" is the value of the "p_idx" parameter you specified in the apex_item calls. So we have apex_application.g_f01, g_f02 and g_f03. But then you discover APEX has the oddity that the "checkbox" array only contains values for the checked rows. Thus if you just check "Jones", the length of g_f02 is 1 and it contains only the empno of Jones - while the other two arrays will contain all (14) rows. So for ...

Recently someone asked me how he could implement multiple authentication schemes for his APEX application. He would like to use (some kind of) Single Sign-on authentication and - as an alternative - an Application Express Authentication. The problem is ... you can only define one Authentication Scheme being "Current" for an application! So how can we solve this issue? First, we need te be aware that multiple applications can share their authentication by using the same cookie. Thus if you specify "MYCOOKIE" as the Cookie Name in Application A as well as in Application B, you can switch from A to B and back without the need of logging in again. It doesn't matter what Authentication Scheme Type you are using! Knowing this, we are halfway our solution. We need two Applications. One - the "real" application - using the Application Express Authentication, let's name this one "LAUNCHPAD". And another one using the Single Sign-on Authenticat...

Nowadays Docker is everywhere. It is one of the main components of Continuous Integration / Continuous Development environments. That alone indicates Docker has to be seen more as a Software Delivery Platform than as a replacement of a virtual machine. However ... If you are running an Oracle database using Docker on your local machine to develop some APEX application, you will probably not move that container is a whole to test and production environments. Because in that case you would not only deliver a new APEX application to the production environment - which is a good thing - but also overwrite the data in production with the data from your development environment. And that won't make your users very excited. So in this set up you will be using Docker as a replacement of a Virtual Machine and not as a Delivery Platform. And that's exactly the way Martin is using it as he described in this recent blog post . It is an ideal way to get up and running with an Oracle d...

So what are my experiences using EECS doing some APEX work? First of all, it is not blazing fast. Probably caused by the latency (as mentioned in my previous post). It is not slow, it is just ok('ish).  Second, and way more important, it runs APEX 5.0.4 ! So whatever Oracle advertises about " Cloud first " ... it doesn't seem to apply to APEX (or APEX on EECS). So this fact alone makes this environment useless for demo, development and presentation purposes at this moment. I heard through the grapevine that 5.1 will be available from April 25 - but that's still a month away. And that rumour could be false. Third, because you don't have access to a SYS account, you can't install something like APEX-SERT . Maybe the install scripts can be adapted for the PDB_ADMIN account - I haven't looked into that yet. Four : As mentioned in an earlier post, within an EECS PDB instance you get one and only one APEX Workspace. You can't add one (no...

In 1.5 week I'll be travelling to and through the Nordics as one of the speakers during the OTN Nordic ACE Tour . It will be quite a busy schedule! On Monday afternoon I'll be flying to Copenhagen and will meet my fellow travellers for the week during a dinner hosted by the Danish Oracle User Group. On Tuesday I have two sessions during the event: One about APEX JavaScript API's and one about APEX 5.1 .  At the end of the day it's off to the airport to catch the flight to Oslo around 7PM. I foresee an airport dinner here.... Wednesday in Oslo I have even three sessions: About the Universal Theme , APEX 5.1 again and APEX and JET .  At the end of the day it's off to the airport again to catch the flight to Helsinki around 7PM. Probably another airport dinner here .... Thursday morning Heli will take us for a short (but brisk) walk through Helsinki, before the Finnish event starts around noon. Here I have only one session, about APEX 5.1 again. And again ......

In my previous blogpost I showed how you can embed an Oracle JET component in your APEX application. Now it is time to make a plugin out of the wisdom we gained doing so. First of all a disclaimer. My intention is to make this plugin and the inner workings as simple as possible. So you can add a lot more functionality, checks etc and therefore add complexity. But this is intended to be as simple as possible. The plugin consists of three parts: a PL/SQL render function, a snippet of JavaScript and a PL/SQL ajax function. The render function is defined as : function render  ( p_region                in  apex_plugin.t_region , p_plugin                in  apex_plugin.t_plugin , p_is_printer_friendly   in  boolean  ) return apex_plugin.t_region_render_result  is   c_region_static_id      constant varchar2(255)  := apex_escap...

In APEX you can define a web service that returns XML as below - all declarative, just a few steps through a wizard. Then generate a report on top of that web service - again just a few clicks through a wizard. The generated query looks like this: select xtab."customerName"      , xtab."customerId"   from apex_collections c,            XMLTable('/Response/S_getCustomerListTableArray/S_getCustomerListArrayItem' passing xmltype001             COLUMNS "customerName" PATH 'customerName'                   , "customerId"   PATH 'customerId'           ) xtab  where c.collection_name = 'CUSTOMERLIST' So the result of the web service is stored in an XMLTYPE column. And it's easy to spot where you're definitions for the Response XPath and Output Parameters are used. But what if your web service returns JS...

At my current project we use Interactive Reports heavily. But in some cases the underlying queries are rather complex and a query without any filter or with just a filter on all columns doesn't perform that well (understatement...). We already implemented a solution to prevent initial loading of the data by adding an additional criterium to the query ( :P400_SEARCH = 'Y' ). The default value of this hidden item is 'N' and on Page Load the value is set to 'Y', so the next search will be executed. But as said, this is good, but not good enough... So this morning I was looking for a solution to "force" the user to enter a query on one of the columns - either through the "search bar column list" (the magnifier on the left side of the search field) or through the Actions > Filter menu. The first attempt was to use a Dynamic Action that fires on change of the search field, but that doesn't work when the user switches existing filter...

Creating a Navigation Bar using static values is very straightforward. Just create a list similar to this example below - and you can easily define multiple levels. Then, in the User Interface Attributes, set that list to be your application's "Navigation Bar List" and specify the template. And the Navigation Bar pops up nicely in the upper right corner of the screen. So far so good. But what if you don't want a Static List, but get a list based on a SQL statement? Then you have to enter a statement that adheres to the this structure: select level, labelValue label, [targetValue] target, [is_current] is_current_list_entry, [imageValue] image, [imageAttributeValue] image_attribute, [imageAltValue] image_alt_attribute, [attribute1] attribute1, [attribute2] attribute2, [attribute3] attribute3, [attribute4] ...

A customer of mine switched to a Singe Sign On Authentication Scheme for their APEX application - using Oracle Access Manager, but that's irrelevant to this case. In the application there's a function to create an - temporarily - "real" APEX user. That APEX user is used in another JavaScript application that uses ORDS and the built-in APEX authentication - thus requiring the existence of APEX users. That function ran flawlessly when the application was still on APEX Authentication. But after switching to SSO, it complained about "ADMIN" privileges .... although the usernames are identical: SSO and APEX Authentication used the same usernames. So it must be something in the authorization / security realm, isn't it? Thus we tried a smart thing and moved that particular function to another - APEX Authenticated - application, while sharing the login credentials via a cookie. But to no avail. Some complaint. So I contacted the APEX Development team and -...

The renewed Calendar Region in APEX is awesome. With just one SQL statement you can provide your users with a calendar view of their data that looks very familiar to what they already know, use and love. Apart from one thing: Navigating to a specific date is cumbersome. The shortest route is: Navigate to the right month (can be any number of clicks)  Switch to "Week view"  Navigate to the right week (between 0 and 4 clicks)  Switch to "Day view"  Navigate to the right dat (between 0 and 7 clicks) Way to many clicks! So how hard can it be to make it easier - more user-friendly? We would like to navigate from both the Month view and the Week view directly into the Day view of a specific date. So with just one click once we have that day in sight. Dynamic Actions to the rescue. Of course. What else... First from the Month view to the Day view: 1. Create a Dynamic Action with the specs as shown to the left. Notice the the Event Scope should be "Dyn...

Apart from the Current Record Indicator in your APEX Reports , another question often pops up at my client sites. A lot of functionality is implemented using a Report and a (Modal) Form. But to access that Form, the user has to click exactly on the small link icon in the first column of the report. So can that be changed, making it more "accessible"? Set the Link Attributes of the column you're using for the link (or the Link definition itself) to:  class="editlink" On Page 0 / Global Page add a Dynamic Action that fires on "Double Click" on the jQuery Selector "tr.clickable". Note: This class was set using an After Refresh Dynamic Action, see the previous blogpost . You can also accomplish that without the Current Record Indicator, but you have to assign the class "clickable" to the TR - you can do that with just the "editlink" class as a selector. The Dynamic Action should execute a JavaScript call:   // E...

Developers with an Oracle Forms background might still remember this nice feature: If you click on a row in an Oracle Form "report" then it can be highlighted - either the full row or just a small first item of the row. The main difference with the Oracle Forms "reports" and the APEX Reports is that in Forms a "report" is usually comparable to an APEX Tabular Form. But a similar feature is frequently asked for - especially at customer sites that are transforming from Forms to APEX. With a few lines of code we can make these customers happy .... Let's get started. First of all your query should contain one column (as "Plain Text") that describes the Primary Key - that might even be a concatenated set of columns. Now we have to tell APEX that this is the column that contains our PK. For a Standard Report just add the CSS class "rowlink" to the column (without the quotes) . For an Interactive Report we need to use the HTML Exp...