Thursday, October 1, 2015

Speed Up Your APEX apps with JSON and Mustache.js

Few days ago I've started to explore solutions to generate (and parse) JSON objects from APEX 5. During this research I came to interesting facts and features.

First of all I found great blog posts from Dimitri Gielis:
and Dan McGhan:
where you can read almost everything that you'll ever got to know about APEX and JSON.

After that I've decided to try it by myself. So I've created simple examples of generating JSON from same source (query from all_objects table) with different methods:
  • Example 1 - using apex_util.json_from_sql (fun fact: not documented)
  • Example 2 - using apex_json package and refcursor
  • Example 3 - using apex_json manual (open_object, wite, close_object)
  • Example 4 - manually with implicit cursor
  • Example 5 - manually with bulk collect
  • Example 6 - using REST service
  • Example 7 - using PL/JSON package

Source in all example was:
SELECT rownum x  
     , owner a  
     , object_name b  
     , object_type c  
  FROM all_objects  
 WHERE rownum <= 500 

Why I was using short aliases in query, like x, a,b,c? Because size of JSON object with short aliases for 500 rows was 29.1 KB and without aliases 43.3 KB (for 7000+ rows with aliases 461.0 KB, without 681.5  KB).
Maybe JSON is not so readable in case of short keys (aliases), but for big JSON object difference in size is significant.

Why only first 500 rows? Because when you generate JSON using REST service there is limit and you can fetch only 500 rows by one request.
Update: you can change this in ORDS configuration file (parameter jdbc.MaxRows). See ORDS documentation for details.

Another fun fact: by using apex_json package size of JSON object was 32.1 KB. In all other examples size was 29.1 KB. I suppose it's because of unnecessary blank lines/characters.

After that I've compared average speed to generate JSON object. The fastest way was to generate it manually in Example 5 (by concatenating strings and by using sys.htp.prn), but I would recommend to use apex_json package. There are many reasons why and I won't explain them now.

When I got my JSON back I wondered what can I do with it in APEX except manually parsing it in jQuery. So I've googled about client side data binding methods implemented in JavaScript and found out Mustache.js.
What is Mustache.js and why I've choose it? It's JavaScript templating engine it's lightweight (min version around 10KB) and simple and you must agree that the name and logo are great!

I won't go into details how to use it. You have everything very well documented here. Maybe I'll cover this in some future post.

So, what did I use it for? It's simple - for templating. :)
I've picked up classic report template from Universal Theme added up Mustache tokens and voilà - I had same report as if it's generated by classic report region but it seemed really fast. Then I thought I should compare execution time and size with classic report region.

I've opened up my Console Window and saw interesting stuff. The size of classic report response (after region refresh) was 122KB and it took about 380ms (in average) to get it (the fastest way with JSON was around 100ms). It was more than 3x slower than JSON generation and the size of object was more than 4x bigger. Isn't this amazing? Should APEX in some future release use JSON and some templating engine for similar stuff?

By then, you can use it in your apps immediately. There are numerus ways, so be creative (see Choose Template option in example)!

For more, visit my presentations at SOIUG 2015 and HROUG 2015 in two weeks.

I hope I'll have more time to write more posts about JSON and Mustache.js soon.

Once again, you can see demo here.

*I've tested execution time and JSON size locally on APEX and Oracle XE

Wednesday, September 23, 2015

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:

Check out demo here.

*Tested on APEX

Wednesday, September 2, 2015

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

Wednesday, July 29, 2015

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}

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.

Monday, July 20, 2015

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.

Tuesday, May 26, 2015

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:

Tuesday, May 12, 2015

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 and (

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.