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! 🙂