In this blog post I will share how the Oracle UTL_HTTP package can been used to enable full stack automation by calling external application and infrastructure REST APIs from within an Oracle 19c or 21c database.
I have previously shared how to perform File System snapshots on a Pure Storage FlashBlade using the UTL_HTTP package, in this post I will show how we can use it to perform Pure Storage FlashArray actions.
For the purpose of this post I will be using an Oracle 21c database and a FlashArray to demonstrate the use of the UTL_HTTP package. I will show how we can use the FlashArray REST APIs to perform a consistent multi-volume storage (Protection Group) level storage snapshot.
Oracle UTL_HTTP
The Oracle UTL_HTTP package provides the ability to perform HyperText Transfer Protocol(Secure) HTTP(s) callouts from SQL and PL/SQL. This can be used to access data locally, over the Internet or call application and infrastructure APIs using HTTP(s).
Note: When the package fetches data from a Web site using HTTPS, it requires an Oracle Wallet which can be created by either Oracle Wallet Manager or the orapki utility. Non-HTTPS fetches do not require an Oracle wallet.
REST APIs
REST APIs (also known as RESTful APIs) provide the ability to interact with applications and infrastructure. Today most modern solutions expose all, or some functionality to public APIs via HTTP(s) methods, for example:
GET | List resources. | GET volume |
POST | Create a resource. | POST volume/v1 |
PUT | Modify a resource. | PUT volume/v1 |
DELETE | Delete a resource. | DELETE volume/v1 |
Create Session
Before we can perform any FlashArray actions we need to create an authorised REST session. We do this using auth/session with the POST method providing the api_token within the body, for example.
POST https://fbstaines02.uklab.purestorage.com/api/1.19/auth/session
{
"api_token": "$APITOKEN"
}
The PL/SQL code below forms the URL as per the format above, sets up HTTP headers, body and performs the request.
The HTTP response status code and authorised username are written to dbms_output for viewing. Note: you may need to SET SERVEROUTPUT ON to see any output.
dbms_output.put_line('Create FlashArray Session');
dbms_output.put_line('-----------------------------------------------');
l_path := p_url || '/api/1.19/auth/session';
l_body := '{"api_token":"' || p_token ||'"}';
-- Prepare for Create session
l_http_request := UTL_HTTP.begin_request(l_path, 'POST' );
-- set Headers
UTL_HTTP.set_header(l_http_request, 'user-agent', 'mozilla/4.0');
UTL_HTTP.set_header(l_http_request, 'content-type', 'application/json');
UTL_HTTP.set_header(l_http_request, 'Content-Length', length(l_body));
-- set Body of request
UTL_HTTP.write_text(l_http_request, l_body);
-- get Response
l_http_response := UTL_HTTP.get_response(l_http_request);
dbms_output.put_line('Response status code: ' || l_http_response.status_code);
-- Loop through the response.
BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
dbms_output.put_line ('Logon: ' || l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
dbms_output.put_line ('>');
END;
-- Loop through HTTP headers
FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(l_http_response) LOOP
UTL_HTTP.GET_HEADER(l_http_response, i, l_name, l_value);
-- DBMS_OUTPUT.PUT_LINE(l_name || ': ' || l_value);
IF l_name = 'Set-Cookie' THEN
l_cookie := l_value;
END IF;
END LOOP;
Protection Group Snapshot
Now we have an authorised REST session we can perform a Protection Group Snapshot calling pgroup with a POST method.
From the table above we can see that to create a snapshot we set snap to true and provide a list of the protection groups names to source, for example.
POST https://fbstaines02.uklab.purestorage.com/api/1.19/pgroup
{
"snap": true,
"source": [
"z-oracle1PG"
]
}
The PL/SQL code again sets-up the URL, HTTP headers, body and performs the request.
The HTTP response status code, created timestamp, protection group snapshot name, and source details are written to dbms_output for review.
dbms_output.put_line('>');
dbms_output.put_line('Perform Protection Group Snapshot of ' || p_pgroup);
dbms_output.put_line('-----------------------------------------------');
l_path := p_url || '/api/1.19/pgroup';
l_body := '{"snap":"true","source": ["' || p_pgroup ||'" ]}';
dbms_output.put_line('>' || l_path);
dbms_output.put_line('' || l_body);
-- Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(l_path, 'POST' );
-- set Headers
UTL_HTTP.set_header(l_http_request, 'user-agent', 'mozilla/4.0');
UTL_HTTP.set_header(l_http_request, 'content-type', 'application/json');
UTL_HTTP.set_header(l_http_request, 'Content-Length', length(l_body));
-- set Body of request
UTL_HTTP.write_text(l_http_request, l_body);
-- get Response
l_http_response := UTL_HTTP.get_response(l_http_request);
dbms_output.put_line('Response status code: ' || l_http_response.status_code);
-- Loop through the response.
BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
dbms_output.put_line (l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;
Perform FlashArray Protection Group Snapshot
The Oracle fa_snapshot procedure expects three parameters FlashArray URL, API Token and Protection Group Name, and uses the provided parameters to perform a Protection Group Snapshot.
Note: as I will be using HTTPS, I have pre-configured an Oracle Wallet, I have previously shared how to create a Wallet, obtain a certificate and and install it, you can re-familiarise yourself here.
Once the procedure has been installed fa_snapshot can be run with:
exec fa_snapshot(‘<URL>’,'<API TOKEN>’,'<Protection Group Name>’);
SQL> exec fa_snapshot('https://z-m20-a.uklab.purestorage.com','5d8ad02f-547d-fc24-bb51-fa0d2b0de973','z-oracle1PG');
Create FlashArray Session
-----------------------------------------------
Response status code: 200
Logon: {"username": "pureuser"}
>
Session Cookie:
session=.eJwtzMEKgzAQhOFXKXPOoWqQ1JcJi7sBwSSySQ5FfHdjKczl44c54Q_RSElSxVK1icFOpXpRzYrl_afKmpV_1rwLFpAqfT1x3BIMytbjiVfrJdiZA3_c6qZgh1Ucz2GYyI7cN46My6AV0UTxOTqaykNcNyCbLjU.YvpjZA.a5jJ8rtAM9cCwhKobja4CnRg
pmo; Expires=Mon, 15-Aug-2022 15:46:52 GMT; HttpOnly; Path=/
>
Perform Protection Group Snapshot of z-oracle1PG
-----------------------------------------------
>https://z-m20-a.uklab.purestorage.com/api/1.19/pgroup
{"snap":"true","source": ["z-oracle1PG" ]}
Response status code: 200
[{"name": "z-oracle1PG.15207", "source": "z-oracle1PG", "created": "2022-08-15T15:16:52Z"}]
PL/SQL procedure successfully completed.
If we logon to the FlashArray WebUI we can see Protection Group snapshot has been created as reported by the database procedure.
Summary
In this post I have shared how the Oracle UTL_HTTP can be used to call external REST APIs to automate application and infrastructure tasks from within an Oracle database.
The example code is available in my GitHub repository