In this blog post, we'll walk through the process of integrating data from an external API into an Oracle database using PL/SQL. Specifically, we’ll use the UTL_HTTP package to fetch JSON data from an API and process it using PL/SQL logic. This is useful for scenarios where you need to retrieve data from external sources and store it in an Oracle table for reporting, analytics, or further processing.
Scenario
Imagine you need to fetch a list of to-do tasks from a public API (in this case, https://jsonplaceholder.typicode.com/todos ) and insert or update these tasks into an API_DATA table in your Oracle database. Each task has an ID, TITLE, COMPLETED.
Steps
- Declare Variables: We will declare variables for HTTP requests, responses, and JSON data.
- Make HTTP Request: Use utl_http to send an HTTP GET request to fetch data from the API.
- Parse JSON Response: Use the json_table function to extract relevant fields from the JSON response.
- Insert/Update Data: Use the MERGE statement to either insert new records or update existing ones based on the id of the task.
- Error Handling: Ensure proper error handling and rollback in case of failure.
Let’s break this down:
1. Declare Variables
We start by declaring the necessary variables, including those to store the HTTP request and response, the JSON response, and fields like task ID, NAME AND DESCRIPTION to hold data from the JSON.
-- Declare variables
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_json_response varchar2(32767);
l_url varchar2(255) := 'https://jsonplaceholder.typicode.com/todos';
l_id number;
l_name varchar2(100);
l_description varchar2(500); Here, the l_url variable holds the URL of the API from which data will be fetched.
2. Declare Cursor to Parse JSON Response
We use the json_table function to convert the JSON response into a relational table format. This makes it easy to loop through and access the individual fields like ID, TITLE, AND COMPLETED for each task.
-- Declare a cursor to parse JSON response
cursor api_cursor is
select *
from
json_table ( l_json_response,'$[*]'
columns (
id number path '$.id',
title varchar2 ( 100 ) path '$.title',
completed varchar2 ( 5 ) path '$.completed'
)
);3. Make the HTTP GET Request
Now, we send the GET request using utl_http.begin_request. We also set the appropriate header to indicate we are expecting a JSON response.
-- Make the HTTP GET request
l_http_request := utl_http.begin_request(
l_url,
'GET'
);
utl_http.set_header(
l_http_request,
'Content-Type',
'application/json'
);4. Get and Read the HTTP Response
After making the request, we get the response using, utl_http.get_response then read the response into the l_json_response variable using utl_http.read_text.
-- Get the HTTP response
l_http_response := utl_http.get_response(l_http_request);
-- Read the HTTP response into the l_json_response variable
utl_http.read_text(
l_http_response,
l_json_response
);5. Process the Data
Next, we loop through the JSON response using a for loop and the api_cursor cursor. For each task in the response, we extract the id, title, and completed fields and use them to update or insert data into the api_data table.
-- Loop through the JSON response and process data
for record in api_cursor loop
l_id := record.id;
l_name := record.title;
l_description := 'Status: ' || record.completed;
-- Perform the MERGE to insert or update data in the api_data table
merge into api_data target
using dual on ( target.id = l_id ) -- Check if the record exists based on 'id'
when matched then update
set target.name = l_name,
target.description = l_description,
target.url = l_url
when not matched then
insert (
id,
name,
description,
url )
values
( l_id,
l_name,
l_description,
l_url ); -- Insert new record if not matched
end loop;The MERGE statement checks if a record with the same id already exists in the api_data table. If it does, it updates the existing record with the new name, description, and url. If not, it inserts a new record.
6. Commit the Transaction
Once all records have been processed, we commit the changes to the database to make sure the data is persisted.
-- Commit the transaction
commit;
dbms_output.put_line('Data successfully processed and updated/inserted into api_data table.');7. Error Handling
To handle any potential errors, we use the exception block. If any error occurs during the execution, it will print the error message and perform a rollback to ensure no partial updates are made to the database.
exception
when others then
dbms_output.put_line('Error occurred: ' || sqlerrm);
rollback;
end;Final Thoughts
This procedure demonstrates how to integrate external API data into an Oracle database using PL/SQL. The utl_httppackage allows us to send HTTP requests, and json_table helps us parse the JSON response in a structured way. The MERGE statement ensures that data is inserted or updated correctly in the target table. By automating this process with PL/SQL, you can efficiently handle data integration tasks from external APIs, making your database more dynamic and up-to-date. This approach can be extended to various use cases, such as integrating external service data, fetching real-time information, or synchronizing data between systems.
Full code
declare
-- Declare variables
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_json_response varchar2(32767);
l_url varchar2(255) := 'https://jsonplaceholder.typicode.com/todos';
l_id number;
l_name varchar2(100);
l_description varchar2(500);
-- Declare a cursor to parse JSON response
cursor api_cursor is
select *
from
json_table ( l_json_response,'$[*]'
columns (
id number path '$.id',
title varchar2 ( 100 ) path '$.title',
completed varchar2 ( 5 ) path '$.completed'
)
);
begin
-- Make the HTTP GET request
l_http_request := utl_http.begin_request(
l_url,
'GET'
);
utl_http.set_header(
l_http_request,
'Content-Type',
'application/json'
);
-- Get the HTTP response
l_http_response := utl_http.get_response(l_http_request);
-- Read the HTTP response into the l_json_response variable
utl_http.read_text(
l_http_response,
l_json_response
);
-- Close the HTTP response
utl_http.end_response(l_http_response);
-- Loop through the JSON response and process data
for record in api_cursor loop
l_id := record.id;
l_name := record.title;
l_description := 'Status: ' || record.completed;
-- Perform the MERGE to insert or update data in the api_data table
merge into api_data target
using dual on ( target.id = l_id ) -- Check if the record exists based on 'id'
when matched then update
set target.name = l_name,
target.description = l_description,
target.url = l_url
when not matched then
insert (
id,
name,
description,
url )
values
( l_id,
l_name,
l_description,
l_url ); -- Insert new record if not matched
end loop;
-- Commit the transaction
commit;
dbms_output.put_line('Data successfully processed and updated/inserted into api_data table.');
exception
when others then
dbms_output.put_line('Error occurred: ' || sqlerrm);
rollback;
end;