Call External HTTPS APIs with Oracle Wallet

This guide is based on the original server-side setup article by my colleague and mentor, Sajjad Hanifa. You can read his full original post here: How to Call External HTTPS APIs with Oracle Wallet & Certificates. He handles the Oracle wallet and server configuration at SH Software Solutions. I work with this setup on the APEX side almost every day, so I wanted to put together a version that connects both worlds.

If you are building something in Oracle APEX and need to call an external HTTPS API, you will hit a wall pretty quickly. The database will not just connect to a secure endpoint out of the box. You need an Oracle wallet, the right certificate imported, and network access configured before any of that works.

I deal with this all the time. Once the server side is set up, calling APIs from APEX becomes straightforward, but getting there the first time can be confusing. This guide walks through the whole thing from start to finish.

Why the Oracle Wallet Exists

Oracle needs a place to store trusted SSL certificates, just like your browser does. That place is the wallet. If the certificate of the API you are calling is not in there, you will get a connection error. Simple as that.

Step 1: Create the Wallet Directory and Import the Certificate

First, get your SSL certificate file (crt.crt) from whoever is running the API. Then create a folder for it inside the wallet directory:

cd /var/lib/docker/volumes/db_oracle-19c-data/_data/wallet/
mkdir apache_pdf
chmod -R 777 apache_pdf

Then use this script to initialize the wallet and import the certificate. Sajjad wrote this and it works really cleanly:

WALLET_PATH="/opt/oracle/oradata/wallet/apache_pdf"
WALLET_PASSWORD="YourPassword"
CERT_FILE="crt.crt"
 
docker exec -it database bash -c "
cd $WALLET_PATH
 
echo 'Creating wallet...'
orapki wallet create -wallet . -pwd $WALLET_PASSWORD -auto_login 2>/dev/null ||
echo 'Wallet already exists, continuing'
 
if [ ! -f $CERT_FILE ]; then
    echo 'ERROR: Certificate file not found!'
    exit 1
fi
 
echo 'Importing certificate...'
orapki wallet add -wallet . -trusted_cert -cert $CERT_FILE -pwd $WALLET_PASSWORD 2>/dev/null ||
echo 'Certificate already imported'
 
echo 'Wallet contents:'
orapki wallet display -wallet . -pwd $WALLET_PASSWORD
echo 'Done. Wallet is ready.'"

Step 2: Grant Network Access (ACL)

The database also needs explicit permission to reach your external host. Run this PL/SQL to allow it:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
    host       => 'dev-pdf-apache.shsoftwaresolution.com',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('http'),
                    principal_name => 'ONBOARDING',
                    principal_type => xs_acl.ptype_db
                  )
  );
END;

Change the host and schema name to match your setup.

Step 3: Store the Connection Details

I always store endpoint URLs, wallet paths, and passwords in a global parameters table. This way you are not hardcoding anything and it is reusable across your whole application:

INSERT INTO GLOBAL_PARAMETER (GLPA_TYPE, GLPA_NAME, GLPA_VALUE, GLPA_ACTIVE_YN)
VALUES ('SYSTEM', 'APACHE_PDF_ENDPOINT', 'https://dev-pdf-apache.shsoftwaresolution.com', 'YES');
 
INSERT INTO GLOBAL_PARAMETER (GLPA_TYPE, GLPA_NAME, GLPA_VALUE, GLPA_ACTIVE_YN)
VALUES ('SYSTEM', 'APACHE_PDF_WALLET_PATH', 'file:/opt/oracle/oradata/wallet/apache_pdf', 'YES');
 
INSERT INTO GLOBAL_PARAMETER (GLPA_TYPE, GLPA_NAME, GLPA_VALUE, GLPA_ACTIVE_YN)
VALUES ('SYSTEM', 'APACHE_PDF_WALLET_PASSWORD', 'YourPassword', 'YES');

Step 4: Test the Connection

Once everything is in place, test it with this PL/SQL block before you wire it into APEX:

DECLARE
    v_url      VARCHAR2(4000);
    v_wallet   VARCHAR2(4000);
    v_pass     VARCHAR2(4000);
    v_response VARCHAR2(32767);
BEGIN
    SELECT GLPA_VALUE INTO v_url    FROM GLOBAL_PARAMETER WHERE GLPA_NAME = 'APACHE_PDF_ENDPOINT';
    SELECT GLPA_VALUE INTO v_wallet FROM GLOBAL_PARAMETER WHERE GLPA_NAME = 'APACHE_PDF_WALLET_PATH';
    SELECT GLPA_VALUE INTO v_pass   FROM GLOBAL_PARAMETER WHERE GLPA_NAME = 'APACHE_PDF_WALLET_PASSWORD';
 
    DBMS_OUTPUT.PUT_LINE('URL:    ' || v_url);
    DBMS_OUTPUT.PUT_LINE('Wallet: ' || v_wallet);
 
    v_response := UTL_HTTP.REQUEST(
        url             => v_url,
        proxy           => '',
        wallet_path     => v_wallet,
        wallet_password => v_pass
    );
 
    DBMS_OUTPUT.PUT_LINE('Response: ' || v_response);
 
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

If you see a response like PDF API OK, you are good to go. From here you can call this inside any APEX process, background job, or PL/SQL procedure just the same way.

Step 5: Use It in APEX

Once the connection is confirmed, calling it from APEX is just standard UTL_HTTP inside a process or a package. Pull your parameters from the global table, pass the wallet path and password, and you are done. I use this pattern for PDF generation, external REST calls, and a few other integrations we run at SH Software Solutions.

Quick Recap

The whole flow is: get the certificate, create the wallet, import the cert, grant ACL access, store your parameters, and then test. Do it once properly and you can reuse the same approach for any HTTPS endpoint.

• Get the SSL certificate from the API team
• Create the wallet directory and import the certificate
• Grant ACL access to the external host
• Store connection details in a global parameters table
• Test with UTL_HTTP before wiring into APEX
• Reuse the same pattern for any HTTPS endpoint

If you are stuck on the server side part of this, that is on Sajjad. If you have questions about the APEX integration side, drop them in the comments and I will help out.

Original article by Sajjad Hanifa: apexnote.de

Hassan Raza
Oracle ACE Apprentice, Pakistan