This blog will guide you on calling external APIs from the oracle database using PL/SQL. Here we will call Stripe APIs. I am using Oracle 18c XE and SQL Developer.
A. Create an Oracle wallet.
Create a folder <wallet> to hold the certificate under the ORACLE_HOME directory. (You can create this folder with a different name and location)
>>cd $ORACLE_HOME
>>mkdir wallet
>>cd wallet
>>orapki wallet create -wallet . -pwd Welcome1 -auto_login
B. Get the target host’s root certificate
In this case, we need a root certificate from https://api.stripe.com.
1) Go to https://api.stripe.com, and click on the lock icon → Certificates from the address bar, as shown in the image
2) And then Certification Path → DigiCert (i.e. root certificate) → View certificate → Details → Copy to file, as shown in the image.
3) This will prompt a window, select Base-64 encoded X.509(.CER), and download the certificate.
C. Add certificate in the wallet
1) Go to the wallet location, and move the downloaded certificate here, and add it to the wallet. My certificate file is named <stripe_root.cer>
<stripe_root.cer>
>>cd $ORACLE_HOME/wallet
>>orapki wallet add -wallet . -trusted_cert -cert stripe_root.cer -pwd Welcome1
>>orapki wallet display -wallet .
D. Add target host to ACL (Access Control Lists)
If we call the APIs, it will give an error as <network access denied by access control list (ACL)>, which means no outbound calls are allowed to this host.
Code
SET SERVEROUTPUT ON;
DECLARE
response CLOB;
BEGIN
SELECT UTL_HTTP.REQUEST(‘https://api.stripe.com/’,NULL,’file:/opt/oracle/product/18c/dbhomeXE/wallet/’,NULL)
INTO response FROM DUAL;
DBMS_OUTPUT.PUT_LINE(response);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR → ‘||SQLERRM);
END;
/
1) Create ACL (Access Control Lists) from a user having the sysdba role, and assign this ACL to a user who is going to call the API. In this case, the user is “API_TEST” user.
select * from dba_network_acls;
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => ‘stripe_test.xml’,
description => ‘ACL for Stripe’,
principal => ‘API_TEST’,
is_grant => true,
privilege => ‘connect’);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => ‘stripe_test.xml’,
principal => ‘API_TEST’,
is_grant => true,
privilege => ‘resolve’);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => ‘stripe_test.xml’,
host => ‘*.stripe.com’);
END;
/
E.Test your API call
The API call is working now. The response says that we did not provide an API key, which is used to authenticate an API call by Stripe.
Here we call https://api.stripe.com/v1/products to list the products which we have created from my stripe account.
Request:
Response:
Code:
SET SERVEROUTPUT ON;
DECLARE
L_HTTP_REQUEST UTL_HTTP.REQ;
L_HTTP_RESPONSE UTL_HTTP.RESP;
L_TEXT VARCHAR2(32767);
RESP CLOB:=’’;
BEGIN
— SET WALLET PATH
UTL_HTTP.SET_WALLET(‘file:/opt/oracle/product/18c/dbhomeXE/wallet/’,NULL);
L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST(‘https://api.stripe.com/v1/products’,’GET’,’HTTP/1.1′);
— SET HEADERS
UTL_HTTP.SET_HEADER(l_http_request, ‘content-type’, ‘application/json’);
UTL_HTTP.SET_HEADER(l_http_request, ‘authorization’, ‘bearer <your key starting from sk_test_%>’);
— GET RESPONSE AND STORE IN A VARIABLE
L_HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE(L_HTTP_REQUEST);
DBMS_OUTPUT.PUT_LINE(‘HTTP response status code →’ || L_HTTP_RESPONSE.status_code);
DBMS_OUTPUT.PUT_LINE(‘HTTP response reason phrase-> ‘ || L_HTTP_RESPONSE.reason_phrase);
— LOOP THROUGH THE RESPONSE.
BEGIN
LOOP
UTL_HTTP.READ_TEXT(L_HTTP_RESPONSE, L_TEXT, 32766);
RESP:=RESP||L_TEXT;
END LOOP;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);
END;
DBMS_OUTPUT.PUT_LINE(RESP);
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);
DBMS_OUTPUT.PUT_LINE(‘ERROR →’|| SQLERRM);
END;
/
F.You are good to go!
That’s it, you can call any external APIs from the Oracle database using PL/SQL.
He is an Oracle DBA at GlobalVox, and is an Oracle Certified OCI Associate Architect.
He aspires to be a database performance tuning expert.
Aside from his curiosity of the tech-world, he has a passion for music and is a flautist
