How to Call HTTPS APIs in PL/SQL Using UTL_HTTP with Oracle Wallet (HTTPS Enabled)
Integrating Oracle databases with external HTTPS APIs requires extra steps—especially handling SSL certificates. Oracle doesn’t trust any HTTPS endpoint by default. You need to configure an Oracle wallet, import the server certificates, and set up ACLs (Access Control Lists) to allow outbound HTTPS calls using UTL_HTTP.
Step 1: Create a Wallet Directory
Create a secure directory where your wallet will reside:
mkdir -p /u01/app/oracle/admin/wallet_api
Step 2: Create Oracle Wallet with Auto Login
Use orapki to create a new wallet with a password and enable auto-login:
orapki wallet create -wallet /u01/app/oracle/admin/wallet_api -pwd DummyPass123 -auto_login
Step 3: Fetch the SSL Certificates from the Target URL
To fetch the certificates (server, intermediate, and root), use OpenSSL:
echo | openssl s_client -showcerts -connect api.example.com:443
This command outputs a certificate chain. Copy each certificate block (from -----BEGIN CERTIFICATE----- to -----END CERTIFICATE-----) and save them as individual files:
server.crt
intermediate.crt
root.crt
Step 4: Import the Certificates into the Wallet
Now, import the required certificates into the wallet:
orapki wallet add -wallet /u01/app/oracle/admin/wallet_api -trusted_cert -cert intermediate.crt -pwd DummyPass123
orapki wallet add -wallet /u01/app/oracle/admin/wallet_api -trusted_cert -cert root.crt -pwd DummyPass123
We can verify wallet contents:
orapki wallet display -wallet /u01/app/oracle/admin/wallet_api
Step 5: Remove Unused or Incorrect Certificates (Optional)
In case you mistakenly added extra certificates, remove them:
# Remove by file
orapki wallet remove -wallet /u01/app/oracle/admin/wallet_api -trusted_cert -cert server.crt -pwd DummyPass123
# Or remove by alias (view alias with 'display')
orapki wallet remove -wallet /u01/app/oracle/admin/wallet_api -alias 'CN=api.example.com' -trusted_cert -pwd DummyPass123
Step 6: Create Network ACL to Allow HTTPS from Oracle
Update ACLs to allow outbound HTTPS traffic for your user (API_USER):
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'api_acl.xml',
description => 'Allow HTTPS for API access',
principal => 'API_USER', -- Replace with your DB user
is_grant => TRUE,
privilege => 'connect'
);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'api_acl.xml',
host => 'api.example.com',
lower_port => 443,
upper_port => 443
);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'api_acl.xml',
principal => 'API_USER',
is_grant => TRUE,
privilege => 'resolve'
);
END;
/
Verify ACLs:
SELECT * FROM dba_network_acls;
SELECT * FROM dba_network_acl_privileges;
Step 7: Call the HTTPS API from PL/SQL
Now, your database can securely call HTTPS APIs:
BEGIN
UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/wallet_api', NULL);
DBMS_OUTPUT.put_line(UTL_HTTP.request('https://api.example.com/status'));
END;
/
Test as below
SELECT UTL_HTTP.request('https://api.example.com/status') FROM dual;
Post a Comment
Post a Comment