A Step-by-Step Guide to Populating a Table with Data from a REST API Using Oracle SQL and PL/SQL

Populating a database table with data from a REST API has become a critical task for businesses that rely on real-time information. Whether it’s financial data, social media insights, or weather updates, having access to accurate and up-to-date information can give organizations a competitive edge. In this article, we will explore the process of easily populating a table with data from a REST API using Oracle SQL and PL/SQL. From setting up the database to writing the code, we will provide step-by-step instructions and examples to help you get started. Whether you are a seasoned database professional or just starting out, this guide will demonstrate how to harness the power of REST APIs and unlock the value of your data.

Here’s an example of how to populate a table with data from the public “https://api.exchangerate.host/latest" REST API using Oracle SQL and PL/SQL. This particular API provides an exchange rate.

The steps will be:

1. Create a table into which we will load the received data
2. Configure Oracle to work with network resources
3. Create a procedure that will take data through the REST API and add the received data to a table
4. Create a job to execute the procedure on schedule

Step 1. Create a table into which we will load the received data

Let’s create a table to store the data received from the REST API. Here’s an example of a table definition in Oracle SQL:

CREATE TABLE exchange_rates (
  currency_code VARCHAR2(3) PRIMARY KEY,
  rate VARCHAR(16)
);

In this example, we are creating a table named “exchange_rates” with two columns: “currency_code” and “rate”. The currency_code column is defined as a primary key and is a variable-length string with a maximum length of 3 characters. The rate column is defined as a varchar as well.

Step 2. Configure Oracle to work with network resources

Now, before we will write a code to access REST API we should prepare our database and give to user some additional rights to communicate through the network.

Create an ACL: we create an ACL with a initial user SCOTT, and the privilege is resolve.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl => 'Connect_Access.xml',
  description => 'Connect Network',
  principal => 'SCOTT',
  is_grant => TRUE,
  privilege => 'resolve',
  start_date => NULL,
  end_date => NULL);
END;
/

Then we have to assign the ACL to a specific network: We open the widest scope ‘*’ to users.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'Connect_Access.xml',
  host => '*',
  lower_port => NULL,
  upper_port => NULL);
END;
/

Checking that we have got rights to connect.

select UTL_INADDR.get_host_name() from dual;

We have to get something like that:

In order to make connections to a secured resource, we need to get the necessary certificate. The easiest way to do this is using a browser. The example below uses the Firefox browser.

Using the browser, go to the URL you are attempting to access from PL/SQL. In this case “https://api.exchangerate.host/latest". Click the lock icon in the URL bar to display the certificate menu and click on the “Secure Connection” tab.

Click the “More information” link and click the “View Certificate” button on the resulting dialog.

And click the “Export” button to save the certificate information.

Select the “X.509 (.PEM)” option and click the “Save” button. Other formats work, but I’ve found this to be the most consistent.

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/DB19G/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/DB19G/wallet -pwd OraPass098 -auto_login

With the wallet created, we can add the certificate we saved earlier.

orapki wallet add -wallet /u01/app/oracle/admin/DB19G/wallet -trusted_cert -cert "/u01/userhome/oracle/Downloads/BaltimoreCyberTrustRoot.crt" -pwd OraPass098

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure. Repeating the previous test now works successfully.

SET SERVEROUTPUT ON
EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DB19G/wallet', NULL);
EXEC show_html_from_url('https://api.exchangerate.host/latest');

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

Step 3. Create a procedure that will take data through the REST API and add the received data to a table

Next, let’s write a PL/SQL block to retrieve data from the REST API and populate the table. Here’s an example using the UTL_HTTP package in PL/SQL:

CREATE OR REPLACE PROCEDURE GET_EXCCHANGE_RATE AS 
  l_url         VARCHAR2(256) := 'https://api.exchangerate.host/latest';
  l_wallet_path VARCHAR2(256) := 'file:/u01/app/oracle/admin/DB19G/wallet';
  l_http_req    UTL_HTTP.req;
  l_http_resp   UTL_HTTP.resp;
  l_response    CLOB;
  l_json        JSON_OBJECT_T;
  l_json2       JSON_OBJECT_T;
  l_key_list2   JSON_KEY_LIST; 
  val_curr      VARCHAR2(3);
  val_rate      VARCHAR2(18);
BEGIN
  UTL_HTTP.set_wallet(l_wallet_path, NULL);
  l_http_req := UTL_HTTP.begin_request(l_url, 'GET');
  l_http_resp := UTL_HTTP.get_response(l_http_req);

  UTL_HTTP.read_text(l_http_resp, l_response);

  l_json := JSON_OBJECT_T.PARSE(l_response);
  l_json2 := l_json.get_object('rates'); 
  l_key_list2 := l_json2.get_keys; 

  FOR counter IN 1 .. l_json2.get_size
    LOOP 
      val_curr := l_key_list2 (counter);
      val_rate := l_json2.get_string(l_key_list2 (counter));
     
      INSERT INTO exchange_rates (currency_code, rate)
      VALUES (val_curr, val_rate);
   END LOOP; 
  
  COMMIT;

END GET_EXCCHANGE_RATE;
/

The UTL_HTTP.read_text function is then used to read the response into a PL/SQL CLOB variable. The JSON_OBJECT_T type is used to parse the response as a JSON object.

In the PL/SQL loop, we are using the INSERT INTO statement to insert the data into the “exchange_rates” table. The l_json.get_keys and l_json.get_string functions are used to retrieve the currency code and rate values from the JSON object, respectively.

Finally, we are committing the changes to the database using the COMMIT statement.

Now we can execute this procedure.

BEGIN
  GET_EXCCHANGE_RATE();
END;
/

Let’s check that table was populated with the data from the REST API.

SELECT
    currency_code,
    to_number(rate)
FROM
    exchange_rates;

Here is the result:

And that’s it! You have successfully populated a table with data from the “https://api.exchangerate.host/latest" REST API using Oracle SQL and PL/SQL. Of course, you may need to modify the code to fit your specific use case, but this should give you a good starting point for

Step 4. Create a job to execute the procedure on schedule

Also you can make a job which would execute this procedure every night.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           => 'my_exchange_rate_job',
    job_type           => 'PLSQL_BLOCK',
    job_action         => 'BEGIN TRUNCATE TABLE exchange_rates; GET_EXCHANGE_RATE; END;',
    start_date         => SYSTIMESTAMP AT TIME ZONE 'UTC',
    repeat_interval    => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
    enabled            => TRUE,
    comments           => 'Job to truncate exchange_rates table and refresh exchange rates every night at midnight.'
  );
END;
/

Other use cases

This approach can be used to fill in data from REST APIs in a variety of scenarios, including but not limited to:

1. Financial data: Populate a table with financial data such as stock prices, exchange rates, or economic indicators.
2. Social media data: Populate a table with social media data such as user information, posts, or comments.
3. eCommerce data: Populate a table with eCommerce data such as product information, prices, and availability.
4. Weather data: Populate a table with weather data such as temperature, humidity, and wind speed.
5. Sports data: Populate a table with sports data such as scores, statistics, and rankings.
6. Geographical data: Populate a table with geographical data such as population, area, or latitude and longitude.
7. News data: Populate a table with news data such as headlines, summaries, and authors.
8. Health data: Populate a table with health data such as disease outbreaks, treatment options, or health outcomes.

In each of these scenarios, the data received from the REST API can be used to populate a database table for further analysis, reporting, or integration with other systems. This approach can also be used to automate data collection and updates, allowing you to stay up-to-date with the latest information from the REST API.

Please feel free to share your thoughts on the above in the comments, and don’t hesitate to connect with me on LinkedIn.