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

3 thoughts on “Check local APEX plugin version against apex.world

  1. Hi Daniel,

    sweet, thank you. I just tested this and had to add the certificate of apex.world to my db wallet first to connect to the webservice.

    There are some data differences though. Some of the plugins seem to have different names in the (local) column APEX_APPL_PLUGING.NAME so i cannot compare them with the webservice column PLG_INT_NAME.

    Examples:
    GPV_IR_TO_MSEXCEL vs. AT.FRT.GPV_IR_TO_MSEXCEL
    apex.enahnced.modal.dialog vs. PRETIUS_APEX_ENHANCED_MODAL_DIALOG
    apex.nested.reports vs. PRETIUS_APEX_NESTED_REPORTS

    Like

    1. Hi Mathias,

      yeah a Oracle Wallet is needed to connect to this SSL protected resource…I just added a note for that to the blog post + several other methods to create the view if e.g. APEX_DATA_PARSER is not available…

      For the internal name issue I can’t do much, thus the developers added the plugin with a different internal name to apex.world…Then you have to be a bit creative by your own to select it correctly, maybe with a LIKE search or something like that…^^

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s