Check local APEX plugin version against apex.world

There’s something new on apex.world: A REST endpoint to retrieve information about listed open source plugins from the apex.world plugins directory!
Just fire up a HTTP GET call to

https://apex.world/ords/apex_world/plugin/list/

to retrieve all plugin information in JSON format…

You can now use this information to compare your locally installed plugins (which of course are installed from apex.world before) with the ones listed on apex.world. In this example I use it to compare the locally installed plugin version to the newest version available, very useful for update checks where you want to know if there’s a newer version of the plugins available!

Just create a view like this in your schema (using APEX_DATA_PARSER: for all DBs and APEX versions >= 19.1):

create or replace force view v_apex_world_plugins as
select apex_world_plugins_t.line_number
      ,apex_world_plugins_t.col001      as plg_int_name
      ,apex_world_plugins_t.col002      as plg_name
      ,apex_world_plugins_t.col003      as plg_desc
      ,apex_world_plugins_t.col004      as plg_type
      ,apex_world_plugins_t.col005      as plg_version
      ,apex_world_plugins_t.col006      as created
      ,apex_world_plugins_t.col007      as updated
      ,apex_world_plugins_t.col008      as apex_versions
      ,apex_world_plugins_t.col009      as demo_url
      ,apex_world_plugins_t.col010      as download_url
      ,apex_world_plugins_t.col011      as homepage_url
      ,apex_world_plugins_t.col012      as like_count
      ,apex_world_plugins_t.col013      as author_name
      ,apex_world_plugins_t.col014      as author_email
      ,apex_world_plugins_t.col015      as author_url
      ,apex_world_plugins_t.col016      as author_twitter
  from table(apex_data_parser.parse(p_content      => apex_web_service.make_rest_request_b(p_url         => 'https://apex.world/ords/apex_world/plugin/list/'
                                                                                          ,p_http_method => 'GET')
                                    ,p_file_name    => 'apex_world_plugins.json'
                                    ,p_file_profile => q'[
{
  "file-type": 4,
  "single-row": false,
  "file-encoding": "AL32UTF8",
  "row-selector": "items",
  "headings-in-first-row": false,
  "csv-enclosed": "\"",
  "columns": [
    {
      "name": "PLG_INT_NAME",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "plg_int_name"
    },
    {
      "name": "PLG_NAME",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "plg_name"
    },
    {
      "name": "PLG_DESC",
      "data-type": 1,
      "data-type-len": 4000,
      "selector": "plg_desc"
    },
    {
      "name": "PLG_TYPE",
      "data-type": 1,
      "data-type-len": 50,
      "selector": "plg_type"
    },
    {
      "name": "PLG_VERSION",
      "data-type": 1,
      "data-type-len": 50,
      "selector": "plg_version"
    },
    {
      "name": "CREATED",
      "data-type": 5,
      "selector": "created",
      "format-mask": "YYYY\"-\"MM\"-\"DD\"T\"HH24\":\"MI:SSTZR"
    },
    {
      "name": "UPDATED",
      "data-type": 5,
      "selector": "updated",
      "format-mask": "YYYY\"-\"MM\"-\"DD\"T\"HH24\":\"MI:SSTZR"
    },
    {
      "name": "APEX_VERSIONS",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "apex_versions"
    },
    {
      "name": "DEMO_URL",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "demo_url"
    },
    {
      "name": "DOWNLOAD_URL",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "download_url"
    },
    {
      "name": "HOMEPAGE_URL",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "homepage_url"
    },
    {
      "name": "LIKE_COUNT",
      "data-type": 2,
      "decimal-char": ".",
      "selector": "like_count"
    },
    {
      "name": "AUTHOR_NAME",
      "data-type": 1,
      "data-type-len": 100,
      "selector": "author_name"
    },
    {
      "name": "AUTHOR_EMAIL",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "author_email"
    },
    {
      "name": "AUTHOR_URL",
      "data-type": 1,
      "data-type-len": 255,
      "selector": "author_url"
    },
    {
      "name": "AUTHOR_TWITTER",
      "data-type": 1,
      "data-type-len": 50,
      "selector": "author_twitter"
    }
  ],
  "parsed-rows": 200
}
]')) apex_world_plugins_t;

Or like this (using XMLTABLE: for all DBs and APEX versions):

create or replace view v_apex_world_plugins as
select apex_world_plugins_t.plg_int_name
      ,apex_world_plugins_t.plg_name
      ,apex_world_plugins_t.plg_desc
      ,apex_world_plugins_t.plg_type
      ,apex_world_plugins_t.plg_version
      ,apex_world_plugins_t.created
      ,apex_world_plugins_t.updated
      ,apex_world_plugins_t.apex_versions
      ,apex_world_plugins_t.demo_url
      ,apex_world_plugins_t.download_url
      ,apex_world_plugins_t.homepage_url
      ,apex_world_plugins_t.like_count
      ,apex_world_plugins_t.author_name
      ,apex_world_plugins_t.author_email
      ,apex_world_plugins_t.author_url
      ,apex_world_plugins_t.author_twitter
  from xmltable('/json/items/row' passing
                apex_json.to_xmltype(apex_web_service.make_rest_request(p_url         => 'https://apex.world/ords/apex_world/plugin/list/'
                                                                       ,p_http_method => 'GET')) columns plg_int_name path
                'plg_int_name'
               ,plg_name path 'plg_name'
               ,plg_desc path 'plg_desc'
               ,plg_type path 'plg_type'
               ,plg_version path 'plg_version'
               ,created path 'created'
               ,updated path 'updated'
               ,apex_versions path 'apex_versions'
               ,demo_url path 'demo_url'
               ,download_url path 'download_url'
               ,homepage_url path 'homepage_url'
               ,like_count path 'like_count'
               ,author_name path 'author_name'
               ,author_email path 'author_email'
               ,author_url path 'author_url'
               ,author_twitter path 'author_twitter') apex_world_plugins_t

Or like this (using JSON_TABLE: for DBs >= 12.1 and all APEX versions):

create or replace view v_apex_world_plugins as
select apex_world_plugins_t.plg_int_name
      ,apex_world_plugins_t.plg_name
      ,apex_world_plugins_t.plg_desc
      ,apex_world_plugins_t.plg_type
      ,apex_world_plugins_t.plg_version
      ,apex_world_plugins_t.created
      ,apex_world_plugins_t.updated
      ,apex_world_plugins_t.apex_versions
      ,apex_world_plugins_t.demo_url
      ,apex_world_plugins_t.download_url
      ,apex_world_plugins_t.homepage_url
      ,apex_world_plugins_t.like_count
      ,apex_world_plugins_t.author_name
      ,apex_world_plugins_t.author_email
      ,apex_world_plugins_t.author_url
      ,apex_world_plugins_t.author_twitter
  from json_table(apex_web_service.make_rest_request(p_url         => 'https://apex.world/ords/apex_world/plugin/list/'
                                                    ,p_http_method => 'GET')
                 ,'$.items[*]' columns(plg_int_name varchar2(250) path '$.plg_int_name'
                         ,plg_name varchar2(250) path '$.plg_name'
                         ,plg_desc varchar2(4000) path '$.plg_desc'
                         ,plg_type varchar2(100) path '$.plg_type'
                         ,plg_version varchar2(50) path '$.plg_version'
                         ,created varchar2(50) path '$.created'
                         ,updated varchar2(50) path '$.updated'
                         ,apex_versions varchar2(100) path '$.apex_versions'
                         ,demo_url varchar2(1000) path '$.demo_url'
                         ,download_url varchar2(1000) path '$.download_url'
                         ,homepage_url varchar2(1000) path '$.homepage_url'
                         ,like_count number path '$.like_count'
                         ,author_name varchar2(250) path '$.author_name'
                         ,author_email varchar2(250) path '$.author_email'
                         ,author_url varchar2(1000) path '$.author_url'
                         ,author_twitter varchar2(100) path '$.author_twitter')) apex_world_plugins_t;

Note: Thus the web service is running over HTTPS a Oracle Wallet is needed to connect to it!
I created a script which creates such a wallet with all valid public root CAs a while back: https://github.com/Dani3lSun/oracle-ca-wallet-creator

And with this select statement you get the local plugin version and the version from apex.world for easier comparison:

select apex_appl_plugins.name
      ,apex_appl_plugins.display_name
      ,apex_appl_plugins.version_identifier as version_local
      ,v_apex_world_plugins.plg_version     as version_apex_world
  from apex_appl_plugins
      ,v_apex_world_plugins
 where apex_appl_plugins.name = v_apex_world_plugins.plg_int_name
   and apex_appl_plugins.application_id = 103

The output should be like that:

And if it differs, it’s time for an update!

Just as easy as 123! 🙂

Oracle APEX Plugin Performance

Last week a customer of mine hired me to look into performance issues they had at larger forms within their APEX inhouse app.
After digging into it, I recognized that they used a lot of plugins in this particular app (~ 90), and of course a lot of known ones from the community like Select2 or Dropzone.

The problematic pages were mostly larger forms with a lot of items, in this case also a lot of plugin items, like mentioned above Select2. This item type plugin was used around 30 times on this page…

So first I had a look into the debug output of the page and I saw that most of the output was the PL/SQL source code of this plugin. So 30 times the APEX engine had to parse and execute the complete PL/SQL source of Select2, and the newest version of it has 773 lines of PL/SQL code…

The second problem I found was that all static files of the plugin (e.g JavaScript and CSS files) came from the database, you will see that in the debug output in APEX > 5.0 environments on the file path of the files that are loaded, e.g:

Load JavaScript file=demo/r/140/files/plugin/53116667758949585/v1/select2-apex.js

File paths which contain the virtual directory “/r/” shows us that the file being loaded comes from the DB, so each time a static file was loaded it hits the DB which returns a BLOB. Of course browsers will cache such a file after getting it once, but in my opinion the best place for static files is the web server itself not the DB…

So I found 2 problems in this case:
1) Large PL/SQL code of a plugin
2) Plugin static files are loaded from DB

A possible solution for both:
1) Transfer the PL/SQL code from the plugin to a real DB object, e.g a PL/SQL package
2) Transfer all plugin static files to the web server

After creating a PL/SQL package (e.g. select2_plg_pkg) with the copied source code from the plugin and moving all static files to the web server, the plugin looked like this:

So lets compare the page performance before and after the changes (30 Select2 items / 2 Dropzone Regions).
With the orginal plugins the page rendering took: ~ 4.3 seconds
With the modified plugins the page rendering took: ~ 1.3 seconds

This is an performance improvement of ~330% !! So the page is 3x faster in rendering than before. To be honest I didn´t expect such an performance increase by changing the plugin code…

If you want to see it in action, here´s a little demo app:
https://orclapex.io/ords/f?p=PLG_PERFORMANCE
Use demo / demo for login!

Conclusion:
As best practise learns us to have as less code in APEX as possible (e.g only function calls), this is also valid for plugins. So be careful in using plugins and always try to review the code. In this case the plugins are not bad coded or something like that, instead it´s the most easy way to share plugins with others, having files and PL/SQL source code inside the plugin. This means that you only need the plugin export SQL file to import and use it.

But some plugins have a lot of source code (here over 700 lines) which makes it problemtatic using such a plugin many times on a page, because this source code have to be parsed again and again.

If you decide to also improve the performance of your plugins like I told you here, be careful, thus the plugin now consists of several parts:
– the plugin export SQL file
– the static files / folder
– the pl/sql package
And if you want to share your plugin or use it in another application you have to import all 3 parts of it!

Another disadvantage would be, if an plugin developer updates the open source plugin you have to catch up the changes by yourself, because you have changed some logic inside of the plugin before…