Dimitri Gielis Blog

Subscribe to Dimitri Gielis Blog feed
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings ...
Updated: 6 days 23 hours ago

Take a few minutes to patch Oracle APEX 5.1

Wed, 03/29/2017 - 13:00
Yesterday a first patch set of Oracle Application Express (APEX) 5.1 has been made available to download.
one-of patches
If you encounter issues, you can ask for support and most likely a bit later a patch is made available through support.oracle.com. The APEX team is doing a great job with this.
For example some people using APEX Office Print had an issue which was caused by a bug in APEX_JSON (which we heavily use behind the scenes). The next day the APEX Dev Team already made a patch available (PSE 25650850).
patch set
Instead of applying those one-off patches, you can wait for a patch set which includes those one-off patches and more. If you didn't move to Oracle APEX 5.1 yet, you can just download the latest version which includes 5.1.1 immediately. 
There're many fixes for the Interactive Grid features, but next to that, many others as well, like for example login issues.
applying the patch set
If you're on Oracle APEX 5.1, search for patch 25341386. Unzip the file, stop the webserver, run @apxpatch, copy the images folder and start the webserver again.About 2 minutes later you're on the latest version. 

Happy patching...

Comma separated search and search with checkboxes in Oracle APEX

Thu, 02/16/2017 - 22:31

When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.

Here’s an example:

Your SQL statement probably looks like this:

select CUSTOMER_ID,
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_STREET_ADDRESS1,
CUST_CITY,
CUST_STATE,
CUST_POSTAL_CODE,
CUST_EMAIL,
CREDIT_LIMIT
from DEMO_CUSTOMERS
where CUSTOMER_ID = :P4_SEARCH

When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.

There’re a couple of options you have, I’ll list three below:

  1. INSTR

    where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
  2. REGEXP_LIKE

    where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
  3. REGEXP_SUBSTR

    where customer_id in to_number((
    select regexp_substr(:P4_SEARCH,'[^,]+', 1, level)
    from dual
    connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null
    ))

Which one to choose? It depends what you need… if you need readability, maybe you find INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).

The Explain Plan for the first SQL looks like this:

The Explain Plan for the last SQL looks like this:

The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:

The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:

where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0

Happy searching your Classic Report :)

Where do you specify the Date Format Mask

Mon, 02/13/2017 - 18:49

When reviewing Oracle APEX applications I often see hardcoded date or timestamp formats.
You can define your date formats in multiple places in your application. In your item or column attributes, as part of your code e.g.TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI’) or if you want to make it more reusable you might create a substitution string. That will all work, but you can make your life easier and for the ones looking or needing to maintain your code…

APEX itself provides in the Globalization attributes (Shared Components) a place where you can define your default date and format masks for your entire applications. I consider this a best practice to use those fields, as it’s defined in one logical place, so when you need to change your format, you do it once and you’re done. In your custom SQL and PL/SQL code you can also reference those format masks by predefined substitution strings:

  • APP_NLS_DATE_FORMAT
  • APP_DATE_TIME_FORMAT
  • APP_NLS_TIMESTAMP_FORMAT
  • APP_NLS_TIMESTAMP_TZ_FORMAT

e.g. TO_CHAR(sysdate, :APP_NLS_DATE_FORMAT)

Here’s a screenshot which shows which substitution string corresponds with which field:

You can define the format mask you want, or you can click the arrow to see most used format masks represented with an example. To make it a bit easier, I put the format mask (in red) next to it, so you see the underlying format mask more easily:

If you need to make the format mask dynamic, for example using different format masks for different language, APEX doesn’t allow you to translate that substitution string through Text Messages, but you can work around it by using your own substitution string and have that dynamically filled. In the Globalization Attributes you would add instead of a hardcoded format mask your own substitution string e.g. &MY_TRANSLATED_DATE.FORMAT.

Changing the label of an item in Oracle APEX dynamically

Tue, 02/07/2017 - 22:37

Today I got the question how to change the label of an item in Oracle Application Express (APEX) based on some condition. I actually had this requirement myself a couple of times, so maybe other people too.

Here’s an example; whenever we change the Source item, we want the Affected Item to change it’s label:

The first thing that comes to mind (if you already know a little bit of APEX); lets use a Dynamic Action: on change of the Source item we will fire (in this example we will only fire when the value is A):

Now which action should we use when the dynamic action fires?

Set Value will typically set the value of an Item, but what about the Label?
If I don’t find the option, I typically look for a plugin or write some code myself. In this case I wrote a bit of JavaScript, for example:

var newLabel = 'My new label for ' + $v('P2_SOURCE_ITEM');
$('#'+$(this.affectedElements).attr('id')+'_LABEL').html(newLabel);

This will set the label to "My new label for " and then the value of the item, at least if you select in the Affected Elements the item that needs the label change.

Whenever I think about writing custom code, my mind says “you should create a plugin for that”.
So I actually started to write an Oracle APEX Plug-in called “Set Label” (https://github.com/dgielis/orclapex-plugin-set-label)

While I was trying the plugin and writing up the things I needed to do, I guess something happend in my mind. I missed the obvious, it suddenly came to my mind there’s a much simpler solution to this…

You can actually use the Set Value action… just add after your item _LABEL, that’s it.

Here’s the result:

Sometimes developing is much more simple than initially thought, you just have to see it :)