Handling client-side messages in APEX 5.1

You may noticed that in APEX 5.1 there's new JS namespace apex.message that is used to handle client-side messages.

Unfortunately, there's no documentation for it yet so I'll try to make a quick introduction to some features that you can use.

Edit (12/2017) : The apex.message documentation is available here.

Important notice: all examples are made in APEX 5.1.0.00.43 which is currently only available on apex.oracle.com. Some features may change in production version.

Most important features/function in new JS library that you can use are:

• apex.message.showPageSuccess - function to dynamically display standard APEX success message,
• apex.message.hidePageSuccess - function that enables you to hide page success message
• apex.message.showErrors - function to display page or inline error messages
• apex.message.alert - function to call new APEX alert dialog
• apex.message.confirm - function to call new APEX confirm dialog

Examples of how to the functions from above are available here.

Be careful when you use apex.message.alert or apex.message.confirm because they are a bit different than build-in browser functions alert() and confirm(). They don't block JS execution so you have to put code following the call into the callback function. For example, if we use following code:

apex.debug.log('Before APEX alert');
apex.message.alert('My Alert Message'
                 , function(){
                     apex.debug.log("You've pressed ok! ");
                   }
);
apex.debug.log('After APEX alert');

the output in JS console (before we click Ok button on alert dialog) will look like this:

And after you press Ok button it will look like this:

So, as you may see, JS code following the apex.message.alert call will execute before we even press Ok button (unlike calling browser's alert() function).

There are some additional advanced features available in new JS library like registering your own templates by calling apex.message.registerTemplates. You can also define custom hooks on some message events like beforeShow or beforeHide (see apex.message.setThemeHooks).

Go, explore and enjoy!

Setting item session state using AJAX in APEX 5

You probably know about the old way how to do this with htmldb_Get JS object:

  var get = new htmldb_Get(null, $v('pFlowId'), null, 0);

  get.add('F_APP_ITEM', vNewItemValue);
  gReturn = get.get();

You can find more about "the old way" here.

But If you've read APEX 5 Release Notes carefully you know that htmldb_Get object is deprecated and it's moved to legacy.js - and you don't what to use legacy JS in your new, modern and shiny APEX 5 applications. You should use apex.server.process or apex.server.plugin for plugins.

So, how can you do it in APEX 5?
It's easy if you want to set current page item values to session state. For this you have a parameter pageItems which can be of type jQuery selector, jQuery or DOM object or array of item names that identifies the page items:

apex.server.process ('MY_PROCESS', 
  {
     pageItems: '#P1_DEPTNO,#P1_EMPNO'
  }, 
  {
    success: function(pData) { ... do something here ... }
  } 
);

But if you want to do some calculations before request or if you want to set application item value to session state you have to dig a bit deeper into the documentation where you can find that with pData parameter of AJAX requests you can set additional parameters that the wwv_flow.show procedure provides.

If you need to set session state of only one item (page or application) you can use parameters p_arg_name and p_arg_value:

apex.server.process ('MY_PROCESS', 
  {
     p_arg_name: 'F_APP_ITEM',
     p_arg_value: vNewAppItemValue
  }, 
  {
    success: function(pData) { ... do something here ... }
  } 
);

If you need to set session state of more than one item (page or application) you have to use parameters p_arg_names and p_arg_values where you have to pass JS array as parameter:

apex.server.process ('MY_PROCESS', 
  {
     p_arg_names: ['F_APP_ITEM','P1_DEPTNO'],
     p_arg_values: [vNewAppItemValue, vNewItemValue]
  }, 
  {
    success: function(pData) { ... do something here ... }
  } 
);

Enjoy!

Tested on APEX 5.0.4.00.12

How to check ORDS version from APEX builder

Probably somebody blogged about this, but I couldn't find it.

So, if you need to check ORDS version and you only have access to APEX Builder you can do it by running following SQL query in SQL Workshop > SQL Commands:

select owa_util.get_cgi_env('APEX_LISTENER_VERSION') 
  from dual;

If you need to check other environment variables you can do it by running following PL/SQL block:

begin 
  owa_util.print_cgi_env;
end;

Edit no. 1 (thanks to Robert Schafer):

You can also query:

select ords.installed_version 
  from dual

Edit no. 2 (thanks to Christian Neumuller)

You can also open Help > About dialog in APEX to see the most important CGI variables and some additional info about APEX and database.

Tested on APEX 5.0.4.00.12 and ORDS 3.0.*

APEX 4 to APEX 5 Migration Tip: Tree Regions

If you're moving your applications to APEX 5 you've probably read about deprecated features. One of the notes there is about the deprecation of jsTree Region.

It doesn't matter if you've turned on legacy JavaScript or jQuery Migrate you'll still get some errors by using old implementation so you have to move them to new APEX tree.

Most probably you'll get error in JS Console like this one:

The reason of this is that $.curCSS() method is removed in jQuery 1.8 and it's not added into jQuery Migrate library.

The next thing you'll probably notice is that there's no icons in new tree.

Old legacy tree:

New APEX tree:

FontAwsome Tree Icons

You can easily use FontAwsome icons there. To do this set Icon Type property to fa in Tree Attributes:

Next step is to modify SQL query of Tree region and set icon column to fa-folder-o

If you want a bit more, for example to have special icon for expanded folder you have to add additional CSS to you page (preferably to CSS file):

.a-TreeView .is-collapsible > .a-TreeView-content > span.fa:before{
  content:"\f115"
}

After those modifications your tree should look like this:

APEX Tree Icons

There's also a way to use APEX font icons for the new APEX tree. Unfortunately, CSS classes are not included by default so you have to add it manually.

In case that you want to use APEX font icons you have to set Icon Type property to a-Icon:

Also you have to modify CSS class in icon column of source SQL query to icon-tree-folder:

The last thing is to add CSS classes (preferably to CSS file and not to inline CSS code page property):

  /* line 583, ../scss/core/IconFont.scss */
  .a-Icon.icon-tree-folder:before,
  .a-TreeView-node.is-expandable > .a-TreeView-content > .a-Icon.icon-tree-folder:before {
    content: "\e0da";
  }

  /* line 588, ../scss/core/IconFont.scss */
  .a-Icon.icon-tree-folder-open:before,
  .a-TreeView-node.is-collapsible > .a-TreeView-content > .a-Icon.icon-tree-folder:before {
    content: "\e0d7";
  }

With APEX font icons your tree should look like this:

Returning selected node value dynamically

If you need to return node value dynamically without submitting page the easiest way to do it is to call $s function inside your SQL query:

Expand tree on page load

To expand tree on page load you have to call: 

apex.widget.tree.expand_all('treeID');

where treeID is value of attribute property Static Tree ID of tree region.
Just put this code into page property Execute when Page Loads.

Edit: You can expand/collapse tree declaratively by using Dynamic Actions Expand/Collapse tree.

Enjoy!

Tested on APEX 5.0.4.00.12 

APEX4 to APEX5 Migration Tip: AJAX calls

If you're moving your APEX applications from APEX 4 to APEX 5 and you've decided that you don't want to include jQuery Migrate Plugin* (to reduce overall size of the JavaScript files loaded) as a result of your AJAX requests you may suddenly see messages like this one:

The reason of this is that there's changed behaviour of AJAX calls from jQuery version 1.9 (APEX 5 is on version 2.1.3, APEX 4 on 1.8.22). Prior to version 1.9 AJAX call that expected a return data type of JSON or JSONP would consider a return value of an empty string to be a success case, but return a null to the success handler or promise. As of 1.9, an empty string returned for JSON data is considered to be malformed JSON and it will throw an error.  

If you're using apex.server.process or apex.server.plugin for your AJAX calls by default expected return data type is JSON. So, if your AJAX callback is not returning JSON object or it's returning empty string you will get the error message like the one above.

There are two solutions to this problem:

1) Explicitly define data type in AJAX calls (for example dataType: 'text')

2) Put dummy return on end of your AJAX callback. It should be: 

• empty JSON object - sys.htp.prn('{}')
• or null string - sys.htp.prn('null')

Enjoy!

*jQuery Migrate Plugin: c/p from APEX help:

The plug-in restores deprecated features and behaviors of jQuery so that old JavaScript code and jQuery plug-ins will still run properly with the jQuery version loaded by Application Express.

If you are confident your application and any used jQuery plug-in does not contain any references to deprecated jQuery features, set this to No to reduce the overall size of the JavaScript files loaded.

Tested on APEX 5.0.4.00.12 

Changed behaviour in APEX5: APEX_PLUGIN_UTIL.CLEANUP_PAGE_ITEM_NAMES

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

P1_ITEM1,,P1_ITEM2

and in APEX 5

P1_ITEM1,P1_ITEM2

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

ODTUG KSCOPE 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.

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.

Enjoy!

Tested in APEX 5.0.3.00.03

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

Example

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.

Solution

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 5.0.3.00.03

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:

begin
  apex_util.set_security_group_id(apex_util.find_security_group_id(:WS_NAME));
end;



Tested on APEX 5.0.3.00.03

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 5.0.3.00.03