Sample Code: Getting JSON format exchange rates using UTL_HTTP and APEX_JSON

on

Problem

You have a requirement to fetch exchange rates from a new provider.
I initially looked at using Apex RESTful services but this looked overly-complicated.
There are a few forums showing how to do this using PL/SQL and specifically the UTL_HTTP utility.

Few mention that you need to "End response", and only one mentioned utility apex_json.
End Response is vital if you don't want to run into a "ORA-29270: too many open HTTP requests".
APEX_JSON is a great utility for extracting the JSON data. It comes standard with every Apex installation.

Solution

Below is a copy/paste which works but hasn't been tidied up. I will run this within a concurrent request and call APIs to update the currency in Oracle.

It fetches from a service provider. See this article for a discussion about which ones to use.

I set up a free account with currencylayer (I removed my access key).

DECLARE
  l_param_list     VARCHAR2(512);

  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;

  l_response_text  VARCHAR2(32767);
  l_values apex_json.t_values;
  j apex_json.t_values;
 

BEGIN

  -- service's input parameters
 
 
  dbms_output.put_line('Test1');
  -- preparing Request...
  l_http_request := UTL_HTTP.begin_request('http://apilayer.net/api/live?access_key=abc'
                                          , 'GET'
                                          , 'HTTP/1.1');
 
  -- ...set header's attributes
  UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');


  -- get Response and obtain received value
  l_http_response := UTL_HTTP.get_response(l_http_request);

  UTL_HTTP.read_text(l_http_response, l_response_text);

  DBMS_OUTPUT.put_line(l_response_text);
  apex_json.parse (l_response_text);
 
  dbms_output.put_line (apex_json.get_varchar2 ('timestamp'));
 
  for v_currency in (select 'USD'||currency_code currency_code
                     from FND_CURRENCIES
                     where enabled_flag = 'Y'
                     and currency_flag = 'Y') loop
     dbms_output.put_line (v_currency.currency_code||' : '||apex_json.get_varchar2 ('quotes.'||v_currency.currency_code));
  end loop;
  UTL_HTTP.end_response(l_http_response);

  EXCEPTION
  WHEN others
    THEN UTL_HTTP.end_response(l_http_response); 

  end;

Acknowledgements

Comments

Post a Comment