Tuesday, October 25, 2016


Unfortunately some things have to be learned in a hard way.

As I'm doing another APEX 4.2 to APEX 5 migration I just learned that there's changed behaviour in function apex_plugin_util.cleanup_page_item_names. Unfortunately, not documented.

If you take a closer look at function source you may see that there's additional call to REPLACE function that replaces double commas with single one:

So, in APEX 4.2 query

will result with

and in APEX 5

Hope that this will save some debug time to somebody! :)

Friday, July 8, 2016


...was the place to be, at least if you are PL/SQL or APEX developer. Numbers say it all: 350+ presentations, 50+ APEX presentations, Sunday symposium, open mic night, Thursday's deep dive session and many more - all of that in only 4 and a half days. Amazing!

One thing was special there - the APEX community. It look like all those 8 years that I'm working with APEX where gathered in one place - APEX dev team and all popular presenters, bloggers and tweeters that you can find out there when you google about APEX and all of them so kind, friendly and eager to share knowledge.

Also, it was great to see that all those rooms with APEX presentations (at least the ones that I've attended) where so full. I don't have anything against ADF, but it was so sad to see 5-10 people on their presentations while APEX rooms where so packed. It reminded me of many projects in which we needed to prove that APEX is the next step when you want to get rid off Oracle Forms. If only I had that picture then. :)

And the content? It wasn't all about the APEX 5.1 as I've expected. It was on the first day on the Sunday symposium when APEX team talked about the new features in 5.1, but that was only a great introduction for the rest of the week. The only problem was that you have to choose only one presentation per session and sometimes it was really hard. There were 3 to 4 parallel APEX presentations + PL/SQL + ORDS and always something interesting.

I had my presentation on Tuesday at 8:30am, first session of the day. I got some great feedbacks so I suppose it went well. If you want to take a look at the slides you can find them here and you are free to contact me anytime if you need some additional information or explanation.

On the last day we had this deep dive sessions that I've really liked. I liked the content and concept because it was a two hour session divided in 10 minute presentations by some of the best and well known APEX speakers. Short and interesting...and there was a hidden gem from Joel, a feature long awaited, about session isolation across multiple browser tabs. From my perspective one of top features in APEX 5.1.

During KSCOPE I wrote some notes on stuff that I want to explore a bit more so if you want to take a look:
  • Electron.js - extending your APEX apps to desktop
  • Camunda - workflow solutions for APEX apps
  • FlowControl - PL/SQL Open Source workflow solution
  • Nightmarejs - high-level automation library
  • Service Workers - a method that enables applications to take advantage of persistent background processing, including hooks to enable bootstrapping of web applications while offline.

And don't forget to take a look at OraOpenSource project. It was mentioned so many times there. Great project with really nice free products that can be very useful in every APEX app.

To conclude, it's time to plan KSCOPE2017, you'll not regret it.

Friday, May 6, 2016

Query APEX dictionary views from non-parsing schema

You may have noticed that if you query any of the APEX's dictionary views (APEX_*) outside the parsing schema you won't get any rows. There's quick solution for this. You only have to grant APEX_ADMINISTRATOR_ROLE to this schema.

    grant APEX_ADMINISTRATOR_ROLE to your_nonparsing_schema;

Be careful, user with this role can execute APEX_INSTANCE_ADMIN package procedures and functions.


Tested in APEX

Wednesday, March 16, 2016

Watch out: validating number type items

I've discovered a strange behavior in APEX that I thought it works out of the box. It's about validation of the number type items.

Simple Case

1) I've created two number items on a page, named P16_MIN_VALUE and P16_MAX_VALUE, and a submit button:

2) Next, I've added the validation that checks if P16_MAX_VALUE is greater or equal than P16_MIN_VALUE


If you put 2 in field Min value and 1 in filed Max value, the validation message is displayed on screen (as expected):

If you put 12 in field Min value and 2 in filed Max value, the validation message is NOT displayed on screen. You'll see success message:

The problem (or unexpected behavior) is that APEX doesn't validates number type items as numbers but as strings.

Demo is available here.


I can't say if this is a bug or expected behaviour because there's no documentation about it (or at least I didn't found it). I know there are functions for getting numeric value of an item: apex_util.get_numeric_session_state and NV but I thought if you have a numeric item and you use it as a bind variable that it will be "binded" like a number.

So, the solution is to explicitly convert your items to numbers or to use one of those functions mentioned above. Your validation should look like this:

...or even better, write your logic in packages with number input parameters.

Enjoy! ;)

Tested on APEX

Saturday, February 13, 2016

Tip of the day: APEX mail & max emails per workspace

It was almost perfect winter day. Sunny with fresh snow. Perfect day for running up and down my favorite mountain.

I was enjoying every bit of nature and all of a sudden: Beeep, beeep! The sound of a SMS. Damn! I've ignored it at first. Two seconds later Beeep, beeep! Beeep, beeep! I thought, it must be something important...and yes it was. It was my boss, messaging me that yesterdays PL/SQL job that sends e-mail from our HR APEX app didn't send e-mail to around 500 clients from 1500 total.
Strange thing, but my Mac was miles away and I couldn't help at the moment (and yes, it was urgent) so I've decided to turn around and head home.

First thing that I've did when I came home was checking APEX mail log. No errors there, no mails in a queue - sounds good. I thought it must be something from the processing part that runs in a PL/SQL job. Then I've checked error log and there it was:

You have exceeded the maximum number of email messages per workspace.  Please contact your administrator. 

Omg, what's that?! It's APEX instance parameter that defines the number of email messages that can be sent with the APEX_MAIL API per workspace per 24 hour period. Omg! :)

To fix this you have to log on as APEX instance admin user and go to Manage Instance > Instance Settings > Email:

So, if you don't want to be bothered in those great moments, don't forget to change it. It can save the day.

Also, If you want to query apex_mail_log or apex_mail_queue dictionary views you have to set workspace (security_group_id) before:


Tested on APEX

Wednesday, January 27, 2016

APEX 5: UT outdated browser (IE) message

Here's a quick tip how to put outdated browser message (for older IE browsers) to your APEX application that uses Universal Theme.

If you take a closer look to page templates of Universal Theme in Header property you can see some conditional comments:

They are perfect for this because they have some CSS classes that you can easily use to check IE version (lt-ie*).
I won't go into details about conditional comments. If you need some more info you can find it here.

Next step is to put a region and message that you want to display if user visits your app with outdated browser. In my case I've created it on the login page just above login region, but you can also put it on global page if you want it on every page.

For this region I've defined Static ID property rgnOutdatedBrowser and Custom Attributes property style="display:none" to hide it initially.

Next thing that you have to do is to put inline CSS rule to page header to display this region for specific IE versions (in this example for IE8 and older):

You can use this CSS classes to display region in :
  • IE6 and older
          .lt-ie7 #rgnOutdatedBrowser {display:block !important}
  • IE7 and older
          .lt-ie8 #rgnOutdatedBrowser {display:block !important}
  • IE8 and older
          .lt-ie9 #rgnOutdatedBrowser {display:block !important}
  • IE9 and older
          .lt-ie10 #rgnOutdatedBrowser {display:block !important}

Here is the outcome of example in IE8:

That's all. Enjoy! :)

*Tested on APEX

Tuesday, October 27, 2015

Remove vertical borders from Universal Theme Interactive Reports

In Template Options of Classic Report region you can set property to see only horizontal borders (property Report Border):

What about Interactive Reports? There's simple way to do it. Just open Theme Roller add this two lines of CSS in Custom CSS property:

  .a-IRR-table td{
    border-left:1px solid transparent

*Tested on APEX