Daniel Hochleitner
Daniel Hochleitner - Blog

Follow

Daniel Hochleitner - Blog

Follow

Check local APEX plugin version against apex.world

Daniel Hochleitner's photo
Daniel Hochleitner
ยทFeb 4, 2020ยท

4 min read

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): [code lang=text] 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 => '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; [/code] Or like this (using XMLTABLE: for all DBs and APEX versions): [code lang=text] 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 => '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 [/code] Or like this (using JSON_TABLE: for DBs >= 12.1 and all APEX versions): [code lang=text] 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 => '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; [/code] 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: [code lang=text] 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 [/code] The output should be like that: And if it differs, it's time for an update! Just as easy as 123! :)

ย 
Share this