Best plsql function ever.....?

Total clickbait title i know and this isn't likely to be the best formatted you'll ever see or the one that has the cleverest use of objects/arrays (i'll leave that to the real experts like Steven) but this is probably the function that i've developed i've been most pleased with - it pulls together a few features used be me in the past - namely:

1) Rest calls from plsql
2) clobs within plsql
3) pipelined functions
4) utl_http and wallets
5) json manipulation

And it does all of that in only about 70 lines of code (Bryn - sorry no EBR in this.....)

So what does this amazing code actually do? Well let me show you the end result of what it makes possible:

That's nothing special i hear you cry from the cheap seats.

Well at first glance maybe not - but what if i told you thats being pulled 'live' from the Azure web portal at Microsoft - intrigued now?

Well let me expand a little further - what I'm doing here is taking what i wrote in my last post and enabling that to be done from plsql.

So in short what the code does is make a rest call to authenticate and get a token then makes a second call with that token to retreive the data I'm actually interested in as Json output, i then parse that json pull out the data I'm interested in and output that via a pipelined fnction.

Now for the impatient ones among you here is the plsql

CREATE OR REPLACE FUNCTION azure
  RETURN rt_tab pipelined
AS
  http_req utl_http.req;
  l_response utl_http.resp;
  l_value CLOB;
  l_raw VARCHAR2(32767);
  l_clob CLOB;
  l_count         NUMBER;
  req_body        VARCHAR2(2000);
  v_client_id     VARCHAR2(500) := 'app-id-from-portal-here';
  v_client_secret VARCHAR2(500) := 'secret password from portal here';
  v_resource      VARCHAR2(300) := 'https://management.azure.com/';
BEGIN
  req_body := 'grant_type=client_credentials&client_id=' || v_client_id ||
  '&client_secret=' || v_client_secret || '&resource=' || v_resource;
  utl_http.set_wallet('file:/home/oracle', 'your-wallet-password-here');
  http_req := utl_http.begin_request(
  'https://login.microsoftonline.com/your-tenant-id-here/oauth2/token' ,'POST',
  'HTTP/1.1');
  UTL_HTTP.set_header (http_req, 'Content-Type',
  'application/x-www-form-urlencoded' );
  UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (req_body));
  UTL_HTTP.write_text (http_req, req_body);
  l_response := utl_http.get_response(http_req);
  dbms_output.put_line(req_body);
  utl_http.read_text(l_response, l_raw);
  UTL_HTTP.end_response (l_response);
  UTL_HTTP.end_request (http_req);
  dbms_output.put_line('value: '||l_raw);
  apex_json.parse(l_raw);
  dbms_output.put_line(apex_json.get_varchar2(p_path=>'access_token'));
  req_body := '';
  l_raw    :='';
  utl_http.set_wallet('file:/home/oracle', 'your-wallet-password-here');
  http_req := utl_http.begin_request(
  'https://management.azure.com/subscriptions/your-subscription-id-here/providers/Microsoft.Compute/virtualmachines?api-version=2016-04-30-preview'
  ,'GET','HTTP/1.1');
  UTL_HTTP.set_header (http_req, 'Content-Type',
  'application/x-www-form-urlencoded' );
  UTL_HTTP.set_header (http_req, 'Authorization','Bearer '||
  apex_json.get_varchar2(p_path=>'access_token' ));
  --   UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (req_body));
  --   UTL_HTTP.write_text (http_req, req_body);
  l_response := utl_http.get_response(http_req);
  dbms_output.put_line(req_body);
  --      utl_http.read_text(l_response, l_raw);
  --   UTL_HTTP.end_response (l_response);
  BEGIN
    DBMS_LOB.createtemporary(l_clob, FALSE);
    LOOP
      UTL_HTTP.read_text(l_response, l_raw, 32767);
      DBMS_LOB.writeappend (l_clob, LENGTH(l_raw), l_raw);
    END LOOP;
  EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    UTL_HTTP.end_response(l_response);
  END;
UTL_HTTP.end_request (http_req);
--     dbms_output.put_line(l_clob);
apex_json.parse(l_clob);
l_count := APEX_JSON.get_count(p_path => 'value');
DBMS_OUTPUT.put_line('Count   : ' || l_count);
FOR row IN 1..l_count
LOOP
  --    dbms_output.put_line(apex_json.get_varchar2(p_path=>'value[%d].name',p0
  -- =>    row));
  --    dbms_output.put_line(apex_json.get_varchar2(p_path=>    'value[%d]
  -- .properties.hardwareProfile.vmSize',p0=>row));
  PIPE ROW(rt_row(apex_json.get_varchar2(p_path=>'value[%d].name',p0=>row),
  apex_json.get_varchar2(p_path=> 'value[%d].properties.hardwareProfile.vmSize'
  ,p0=>row)));
END LOOP;
--     dbms_output.put_line(l_clob);
DBMS_LOB.freetemporary(l_clob);
RETURN;
END;
/

However that has some small dependencies - which are shown here - these are to allow the pipe row calls to have something to output into.

These are shown here

CREATE TYPE rt_row
AS
  OBJECT
  (
    servername VARCHAR2(4000),
    serversize VARCHAR2(4000) );
/
CREATE TYPE rt_tab
IS
  TABLE OF rt_row;
/

So thats essentially just defining an output row, and then an output table based on that type. You can easily extend the number of rows should you require more 'columns' in it.

Here is an attempt at an annotated version (sorry for small text) which maybe helps explain

stage1 - set up variables
stage 2 - do initial rest call to get token
stage 3 - do actual call we want to get the data
stage 4 - parse and output the json (raw json shown just below which may help explain the json syntax when you can see the source data)

So thats the raw code you need - but there are some dependencies/prerequisites you need to work though to make this executable.

These are (in no particular order):

1) You must be on Oracle version 12.1 (12.2 doesn't work as calls to https sites where the certificate name does match the CN fail and the supposed new feature to address this - the https_host parameter does not seem to work at all). 11.2 and earlier may work i just didn't test those.
2) Apex v5 must exist - this is needed to be able to make the apex_json calls (this could be done using other json functionality in 'core' 12c but i thing that would be harder to code
3) databases ACL's must allow the session that runs the function to make http/https calls out - if you are just lazy and want to switch this off - take a look at this old post
4) You must have a wallet created containing the public certificates (and trust chain for the https sites you are visiting - this is slightly annoying and i wish there was just a 'just do it and dont worry about certs option' - but there isn't.
5) oh and don't forget to 'set scan off' when running this in sqlplsu to stop the embedded & characters being interpreted as variables to prompt for

For option 4 just to explain a little more as this isn't a thing you do every day - the way to achieve this is to browse to the destination url in any browser - from there you should be aple to open the certificate from some option in the browser and then download all the relevant certificates in the trust chain to a file - you then need to copy all these to the database server and load them into the wallet

The wallet steps then essentially come down to

1) orapki wallet create -wallet /home/oracle -auto_login
that create the wallet

2)  orapki wallet add -wallet /home/oracle -trusted_cert -cert /tmp/managementazure.p7c
that loads the cert in (make sure to repeat for all certs in the trust chain)

There is more detail here well explained by Tim.

Once all of that lot is in place you can then just say

SELECT
  *
FROM
  TABLE(azure);

Which gives you an Oracle tabularized view of the data from Azure. This same principal could apply to anything that uses rest - or indeed any website that can be made to return JSON. I would imagine (though don't know) that the Oracle cloud has something similar and the same code could be slightly changed to work their too.

I've done a simple fetching data example here - bt the Azure API's also expose almost all Azure functions in this way so it would be perfectly possible to build some plsql where you could run something like

exec create_server('RHEL','west_europe','really big');

And it would go off and create you a server in Azure - neat huh? (no use case for that at the moment but the core code is mostly written in the function above).

So what do you think - best function ever....?