APEX by G: October 2015

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

First of all I found some great blog posts from Dimitri Gielis:

and Dan McGhan:

where you can read almost everything that you'll ever need to know about APEX and JSON.

After that I've decided to try it by myself. So I've created a simple examples of generating JSON from same source (query from all_objects table) with the 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 the query, like x, a,b,c? Because size of a 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 a 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 5.0.1.00.06 and Oracle XE 11.2.0.2.0

Tuesday, October 27, 2015

Remove vertical borders from Universal Theme Interactive Reports

Thursday, October 22, 2015

Explore the mysteries of APEX Builder

Thursday, October 1, 2015

Speed Up Your APEX apps with JSON and Mustache.js