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

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! :)

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-header{
    box-shadow:none
  }
  .a-IRR-table td{
    border-left:1px solid transparent
  }

*Tested on APEX 5.0.1.00.06.

APEX 5: Sticky Regions

You know that in APEX 5 you can (for IR) define how you would like to display report headings

• Fixed to Region
• Fixed to Page
• None

By using same API (not documented) you can make your regions "sticky".
First, you have to set Static Region ID (for example rgnSticky) of your sticky region.

After that add onload JS code:
$("#rgnSticky").stickyWidget({toggleWidth:true});

Check out demo here.

*Tested on APEX 5.0.1.00.06.

Using PL/SQL block as result set for RESTful services

Maybe I'll save some time to someone with this post... :)

So, if you're using RESTful Services and your source type is PL/SQL you should wrap your code with BEGIN and END keywords.
I'm not used to do this in APEX since in every place where you're writing PL/SQL code this is done implicitly and I was surprised that here I had to do it explicitly.

I've tested this with APEX 5.0.1.00.06.

APEX 5: fixed IR column width

Setting fixed width of columns in IR in APEX 5 is a little different than in older APEX version. Here's an simple example of setting fixed width of Hiredate column of Employees report.

First you have to set column static ID:

Then you have to add CSS to page (or static file):

If you have more than one report on the page you can prefix CSS with region static ID, for example:

#regionStaticId th#colHiredate, #regionStaticId td[headers=colHiredate]{width:100px}

Update:
Use min-width CSS property instead of width (it fixes problem if you have many columns in report)
#regionStaticId th#colHiredate, #regionStaticId td[headers=colHiredate]{min-width:100px}

You can check out demo here.

Dynamic Tree Search Plug-In

If you've missed it in my Twitter post, here's new Plug-In for dynamic APEX tree search.
This Item Plug-In enables you to create dynamic search for APEX tree regions.
You can get more info in demo application.

APEX 5: Add Spinner to Classic Reports Plugin

I've created a little APEX5 Dynamic Action plugin that adds spinner on dynamic refresh of classic report regions (like you have for Interactive Reports).

You can see info and download it here: https://goo.gl/84ITaV

Tabular Form Bug

I found bug in tabular forms created over tables that have columns with timestamp datatype (datepicker fields).
If you try to update any column value you'll get error message like this one:

Current version of data in database has changed since user initiated update process. current row version identifier = "CB89E9B0740B64051B6CA00FD81E58166C2288FA" application row version identifier = "3641B87EB52532E016A0D8377AA8F181A6E2FBCA" (Row 1)

I'm getting same error on APEX 4.2.5.00.08 and 5.0.0.00.31 (apex.oracle.com).

The bug was reported to APEX team and it will be fixed in future releases (5.1).

Quick fix for this is applying explicit format mask for timestamp datepicker columns.

Thanks to Robert and Valentino Orehoci for pointing this out and Marc Sewtz for quick fix.

APEX 5: Issues with configuration of Static Files (ORDS)

If you're getting this alert window when going to your APEX 5 login screen:

after upgrading from older versions of APEX you're probably missed this paragraph in installation documentation:
When using Oracle REST Data Services as your web listener, static files are served using RESTful service module built into Oracle Application Express. Therefore, you must configure RESTful Services in new installations and in upgrade installations when RESTful Services were not configured in a previous release.

To fix it don't forget to configure Oracle REST Data Services by executing this script @apex_rest_config.sql.